Fetching Post and Comment Distribution of Accounts [January]

in #hive-1635212 years ago

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


accountposts_in_hive_trpost_in_otherspost_totalpost_in_tr_rate
1flsfserkan200201
2bilginhilal170171
3bilgin70160161
4passenger777150151
5mehmetfix149230.61
6mukadder130131
7ahmetay123150.8
8baboz111120.92
9barika100101
10ipexito108180.56
11wagner329091
12sudefteri8081
13incublus86140.57
14catslovers8190.89
15princeofbeyhive7180.88
16sezermehmet7071
17bilginselcuk6061
18dolunay186061
19ezgicop5160.83
20canser5051
21kirazay5051
22mttok5380.63
23snlbaskurt4150.8
24sunsan3031
25trliste3250.6
26videoaddiction3250.6
27benyamin3031
28akkann3470.43
29dusunenkalpp2021
30kedi2570.29
31hikergirl216180.11
32motivationrainn2021
33mrknktr1120.5
34rsntpl110110.09
35nusrat011120.5
36yagmurg1011
37tragiclady119200.05
38damla1011

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


accountcomments_count_trcomments_out_of_trcomment_totalcomment_tr_rate
1mehmetfix18389610790.17
2baboz1691112800.6
3sudefteri15441580.97
4bilginhilal14651510.97
5incublus1454545990.24
6flsfserkan13701371
7ecency135496651010.03
8princeofbeyhive12921310.98
9hivebuzz12623840239660.01
10ipexito1251222470.51
11bilgin70122111330.92
12passenger77712261280.95
13wagner3210611070.99
14mukadder9941030.96
15barika740741
16ahmetay73391120.65
17catslovers561570.98
18pinmapple49174717960.03
19ezgicop464500.92
20trliste438510.84
21bilginselcuk430431
22tipu42187219140.02
23videoaddiction422783200.13
24sezermehmet410411
25damla407470.85
26dolunay18405450.89
27kirazay390391
28luvshares3411634116680
29pizzabot34863186650
30hikergirl3249810.4
31mttok290291
32canser290291
33pgm-curator2812740127680
34pinkfurby283310.9
35poshtoken2811935119630
36dusunenkalpp262280.93
37snlbaskurt212230.91
38benyamin200201
39isnochys174975140.03
40akkann1712290.59
41beerlover16342634420
42sunsan151160.94
43lolzbot1514919149340
44motivationrainn100101
45muratkbesiroglu834420.19
46brittandjosie89189260.01
47queenstarr81321400.06
48hive-14554081922000.04
49mrknktr74110.64
50svanbo75105170.01
51gokturk706061
52tagmout62562620.02
53frankevor652580.1
54savvytester54674720.01
55tattoodjay58938980.01
56youarealive511101111060
57kedi413170.24
58uzercanan4150.8
59arcange42252290.02
60macchiata44534570.01
61dsc-r2cornell48028060
62browniegirl4150.8
63dangab482860.05
64charmingcherry31421450.02
65elgatomayor334370.08
66bbhbot3282328260
67rsntpl325280.11
68ibbtammy31381410.02
69vocup32012040.01
70arduilcelebren32352380.01
71muhammedfit3031
72yagmurg3031
73librepensadora348510.06
74splinterboost3249524980
75izuki.midoriya336390.08
76treasuree23003020.01
77jane128925996010
78lovesniper21941960.01
79jte1023223250.08
80killerwot21251270.02
81graciousvic22922940.01
82mikezillo22202220.01
83ladytitan21471490.01
84shakavon275770.03
85littlebee42130813100
86missleray2106910710
87oneplanet2021
88nameless1621381400.01
89qurator26696710
90cryptospa21161180.02
91derangedvisions22062080.01
92pranavgtd236380.05
93fernandoylet21341360.01
94ramon202421381400.01
95clarissaaaa2460.33
96wittyzell23233250.01
97abhay269521921940.01
98oldfool150510.02
99hivewatchers12412420
100technicalside14174180
101adcreatordesign16596600
102silviamaria124250.04
103meme.bot1623462350
104tht134350.03
105bigorna1134350.03
106visionaer30031011
107mimtan177780.01
108vragolana179800.01
109cloverhive1780.13
110wanderingmoon185860.01
111mizuosemla159600.02
112wengyyy19100.1
113daltono15005010
114leogolj156570.02
115neoxian-city120210.05
116wilfredocav11891900.01
117discovery-it17337340
118manncpt11011020.01
119ptaku112130.08
120cuddlekitten13573580
121rocinanteprimo111120.08
122mipiano16266270
123redditposh13083090
124dimascastillo9011141150.01
125wayuu-reg158590.02
126jijisaurart12862870
127sassy.cebuana151520.02
128erikah17377380
129maccmacc158590.02
130kerrislravenhill15115120
131josiebalderas1011
132francyrios75140410.02
133noemilunastorta12142150
134dodovietnam11171180.01
135lionsaturbix176770.01
136ayane-chan11381390.01
137tragiclady115160.06
138edebiyat1011
139travelfeed11151160.01
140baycan1120.5
141elwnyx1011
142adysscheryl14164170
143emrebeyler1340.25
144agmoore12252260
145ewkaw12272280
146aly.stor124250.04
147sydechan144450.02
148brataka11041050.01
149nasseir122230.04
150naymhapz15475480
151not-here114150.07
152lordshah11911920.01
153jhero2211141150.01
154mahmutsahintepee1011
155melinda01010016306310
156proofofbrainblog12202210
157stefano.massari14464470
158sperosamuel151101910200

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

Sort:  

May I please be removed from this list ? thank you.

Sure, all mentions removed

Thank you.

Thank you for your effort and for the mention. ☺️

Thank you for mentioning me even why I’m not part of Tr 🤍
I just like to comment when i see beautiful posts.

It is always good to make an analysis of the statistics because many ideas that can be implemented in the near future for the improvement and good management of projects depend on them...
Greetings @kedi good job!


Siempre es bueno hacer un análisis de las estadisticas porque de ellas dependen muchas ideas que se pueden implementar en un futuro cercano para el mejoramiento y una buena conducción de proyectos...
Saludos @kedi buen trabajo!


The rewards earned on this comment will go directly to the people( @fernandoylet ) sharing the post on Twitter as long as they are registered with @poshtoken. Sign up at https://hiveposh.com.

Ocak ayında ne kadar da sessiz olduğum belli oldu :D Güzel istatislik

Thank you for the mention. Much appreciated.

thanks for this work , good luck 👏

Güzel bir çalışma olmuş emeğinize sağlık.

Good evening, why I am here??

You must have posted or commented in hive turkish community and this is some data regarding that.

Yes it's true I commented in Hive turkish, but I can't post there

That is weird, why though?

Because I am not Turkish,

Health to your hands. Nice work. Thanks.

This is an analysis related to the Hive Turkish Community. Applicable to any other community, changing the number 124065 in the code. Interesting.

Exactly what I am trying to convey

Selam kardeşim, TR camiasında nişanlandığımı görmek güzel. Pakistan'dan Aşk

Could you score comment by engagement example likes are manipulated by bots so a comment quality score would gauge if the poster is creating content that encourages conversation. That way we can see who’s posting is driving legitimate interactions vs bots.

@tagmout I’m just using you as an example okay.

You see their comment and mine. One is more engaging than the other.

Where they are asking why are they included in the post. Vs my comment

Both or comments by real people by one is more engaging than the other.

Don’t pay attention to what I say though I don’t know how coding works to save my life… but I can grow some food 😸

Using comment depth here might work. It may be necessary to scan the comment histories of the accounts in order to eliminate bots. This is the first way that comes to my mind to detect whether the account is a bot or not.

Good luck growing food btw 🤠

Happy to be a help! Not that bots are not important curation bots are helpful it’s just nice to know what’s your percentage between bot engagement and actual human engagement.

Linkini paylaştığın HiveSQL aracılığıyla mı yaptın bunu? Bence çok iyi istatistikler. Ellerine sağlık.
Bunu nasıl yaptığınla ilgili bana biraz daha ipucu verirsen çok makbule geçer 😀
!LUV !PGM

Tabii ki, çekmek istediğin data varsa yardımcı olmaya çalışırım

Teşekkür ederim.
Discorddaki kullanıcı adın ne bu arada? Belki oradan yazarım 😀

Posted via D.Buzz

Yaser#5143

Tamamdır istek attım sana, teşekkür ederim 😀

Posted via D.Buzz

Congratulations @kedi! You have completed the following achievement on the Hive blockchain And have been rewarded with New badge(s)

You distributed more than 12000 upvotes.
Your next target is to reach 13000 upvotes.

You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Check out our last posts:

Hive Power Up Day - February 1st 2023
The Hive Gamification Proposal