Fetching Post and Comment Distribution of Accounts [January]

Anyone here who doesn't love Saturdays? I guess not. It is one of the best days to spare time for arbitrary works. Today, I have tried to get some results from activity related to posts on the Hive blockchain and have chosen the Hive-TR as the community to test the queries (:

Two different scenarios occurred to me. First, I fetched the list of accounts that was active in the Hive-TR community and how many posts they shared with the community as of January from the database. After that, I joined the number of posts of these accounts outside of Hive-TR to the table. And, we can see the percentage distribution of the posts sent by users who have shared blog posts on Hive-TR, which has been active in the last 1 month. The first table was like below.

Blog Stats of Active Accounts in Hive-TR in January

Similarly, I fetched accounts who commented at least once in January on Hive-TR from the database in the second scenario. The other one I selected can give some insight with the activity percentages of accounts thro Hive.

Comment Stats of Active Accounts in Hive-TR in January

January isn't over yet, but I wanted to do something like this while there was time on Saturday. Here I have used only two basic metrics. Post and comment counts.. What can be done with the percentage of activity in the community relative to the general distribution? I'm not sure for now, but it might be useful in the future. It is not difficult to predict that if the Hive price increases, the number of users in the community will increase (:

-- Blog Stats of Active Accounts

WITH CTE AS (

SELECT CONCAT('@',t1.author) AS account, t1.posts_in_hive_tr, 
        (CASE WHEN post_in_others IS NULL THEN 0 ELSE post_in_others END) AS post_in_others, 
        t1.posts_in_hive_tr + COALESCE(post_in_others,0) AS post_total
FROM

    (SELECT author, count(*) AS posts_in_hive_tr  FROM Comments
    WHERE category = 'hive-124065' AND depth = 0 AND created > '2023-01-01 00:00:00'
    GROUP BY author) AS t1

        LEFT JOIN

    (SELECT author, COUNT(*) AS post_in_others FROM Comments
    WHERE 
    author IN (SELECT DISTINCT author FROM Comments WHERE category = 'hive-124065' 
                                AND depth = 0 AND created > '2023-01-01 00:00:00')
        AND category != 'hive-124065' AND depth = 0 AND created > '2023-01-01 00:00:00'

    GROUP BY author) AS t2

ON t1.author = t2.author )

SELECT *, 
    ROUND(CAST(posts_in_hive_tr AS FLOAT) / 
    CAST(post_total AS FLOAT),2) AS 'post_in_tr_rate' FROM CTE

ORDER BY posts_in_hive_tr DESC

-- Comment Stats of Active Accounts

WITH CTE_COMMENTS AS (
    SELECT CONCAT('@',t1.author) AS account, t1.comments_count_tr, 
            (CASE WHEN t2.comments_count_others IS NULL THEN 0 ELSE t2.comments_count_others END) AS comments_out_of_tr,
            t1.comments_count_tr + COALESCE(comments_count_others,0) AS comment_total
    FROM 
        (SELECT author, count(*) AS comments_count_tr  FROM Comments
        WHERE category = 'hive-124065' AND depth > 0 AND created > '2023-01-01 00:00:00'
        GROUP BY author ) t1

            LEFT JOIN

        (SELECT author, count(*) AS comments_count_others  FROM Comments
        WHERE category != 'hive-124065' AND depth > 0 AND created > '2023-01-01 00:00:00'
            AND author IN (SELECT DISTINCT author FROM Comments
                            WHERE category = 'hive-124065' AND depth > 0 
                            AND created > '2023-01-01 00:00:00')
        GROUP BY author ) t2

    ON t1.author = t2.author
)


SELECT *, 
    ROUND(CAST(comments_count_tr AS FLOAT) / 
    CAST(comment_total AS FLOAT),2) AS 'comment_tr_rate' FROM CTE_COMMENTS

ORDER BY comments_count_tr DESC

Any suggestions will be valuable if you have any of idea for user activity tracking. Please leave a comment, if you have metrics to suggest. I put the codes above in case there is anyone who are curious in the future.

You can use the queries after connecting to HiveSQL.

Enjoy your weekend!
Yaser