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

---

	account	posts_in_hive_tr	post_in_others	post_total	post_in_tr_rate
1	flsfserkan	20	0	20	1
2	bilginhilal	17	0	17	1
3	bilgin70	16	0	16	1
4	passenger777	15	0	15	1
5	mehmetfix	14	9	23	0.61
6	mukadder	13	0	13	1
7	ahmetay	12	3	15	0.8
8	baboz	11	1	12	0.92
9	barika	10	0	10	1
10	ipexito	10	8	18	0.56
11	wagner32	9	0	9	1
12	sudefteri	8	0	8	1
13	incublus	8	6	14	0.57
14	catslovers	8	1	9	0.89
15	princeofbeyhive	7	1	8	0.88
16	sezermehmet	7	0	7	1
17	bilginselcuk	6	0	6	1
18	dolunay18	6	0	6	1
19	ezgicop	5	1	6	0.83
20	canser	5	0	5	1
21	kirazay	5	0	5	1
22	mttok	5	3	8	0.63
23	snlbaskurt	4	1	5	0.8
24	sunsan	3	0	3	1
25	trliste	3	2	5	0.6
26	videoaddiction	3	2	5	0.6
27	benyamin	3	0	3	1
28	akkann	3	4	7	0.43
29	dusunenkalpp	2	0	2	1
30	kedi	2	5	7	0.29
31	hikergirl	2	16	18	0.11
32	motivationrainn	2	0	2	1
33	mrknktr	1	1	2	0.5
34	rsntpl	1	10	11	0.09
35	nusrat01	1	1	2	0.5
36	yagmurg	1	0	1	1
37	tragiclady	1	19	20	0.05
38	damla	1	0	1	1

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

---

	account	comments_count_tr	comments_out_of_tr	comment_total	comment_tr_rate
1	mehmetfix	183	896	1079	0.17
2	baboz	169	111	280	0.6
3	sudefteri	154	4	158	0.97
4	bilginhilal	146	5	151	0.97
5	incublus	145	454	599	0.24
6	flsfserkan	137	0	137	1
7	ecency	135	4966	5101	0.03
8	princeofbeyhive	129	2	131	0.98
9	hivebuzz	126	23840	23966	0.01
10	ipexito	125	122	247	0.51
11	bilgin70	122	11	133	0.92
12	passenger777	122	6	128	0.95
13	wagner32	106	1	107	0.99
14	mukadder	99	4	103	0.96
15	barika	74	0	74	1
16	ahmetay	73	39	112	0.65
17	catslovers	56	1	57	0.98
18	pinmapple	49	1747	1796	0.03
19	ezgicop	46	4	50	0.92
20	trliste	43	8	51	0.84
21	bilginselcuk	43	0	43	1
22	tipu	42	1872	1914	0.02
23	videoaddiction	42	278	320	0.13
24	sezermehmet	41	0	41	1
25	damla	40	7	47	0.85
26	dolunay18	40	5	45	0.89
27	kirazay	39	0	39	1
28	luvshares	34	11634	11668	0
29	pizzabot	34	8631	8665	0
30	hikergirl	32	49	81	0.4
31	mttok	29	0	29	1
32	canser	29	0	29	1
33	pgm-curator	28	12740	12768	0
34	pinkfurby	28	3	31	0.9
35	poshtoken	28	11935	11963	0
36	dusunenkalpp	26	2	28	0.93
37	snlbaskurt	21	2	23	0.91
38	benyamin	20	0	20	1
39	isnochys	17	497	514	0.03
40	akkann	17	12	29	0.59
41	beerlover	16	3426	3442	0
42	sunsan	15	1	16	0.94
43	lolzbot	15	14919	14934	0
44	motivationrainn	10	0	10	1
45	muratkbesiroglu	8	34	42	0.19
46	brittandjosie	8	918	926	0.01
47	queenstarr	8	132	140	0.06
48	hive-145540	8	192	200	0.04
49	mrknktr	7	4	11	0.64
50	svanbo	7	510	517	0.01
51	gokturk70	6	0	6	1
52	tagmout	6	256	262	0.02
53	frankevor	6	52	58	0.1
54	savvytester	5	467	472	0.01
55	tattoodjay	5	893	898	0.01
56	youarealive	5	11101	11106	0
57	kedi	4	13	17	0.24
58	uzercanan	4	1	5	0.8
59	arcange	4	225	229	0.02
60	macchiata	4	453	457	0.01
61	dsc-r2cornell	4	802	806	0
62	browniegirl	4	1	5	0.8
63	dangab	4	82	86	0.05
64	charmingcherry	3	142	145	0.02
65	elgatomayor	3	34	37	0.08
66	bbhbot	3	2823	2826	0
67	rsntpl	3	25	28	0.11
68	ibbtammy	3	138	141	0.02
69	vocup	3	201	204	0.01
70	arduilcelebren	3	235	238	0.01
71	muhammedfit	3	0	3	1
72	yagmurg	3	0	3	1
73	librepensadora	3	48	51	0.06
74	splinterboost	3	2495	2498	0
75	izuki.midoriya	3	36	39	0.08
76	treasuree	2	300	302	0.01
77	jane1289	2	599	601	0
78	lovesniper	2	194	196	0.01
79	jte1023	2	23	25	0.08
80	killerwot	2	125	127	0.02
81	graciousvic	2	292	294	0.01
82	mikezillo	2	220	222	0.01
83	ladytitan	2	147	149	0.01
84	shakavon	2	75	77	0.03
85	littlebee4	2	1308	1310	0
86	missleray	2	1069	1071	0
87	oneplanet	2	0	2	1
88	nameless16	2	138	140	0.01
89	qurator	2	669	671	0
90	cryptospa	2	116	118	0.02
91	derangedvisions	2	206	208	0.01
92	pranavgtd	2	36	38	0.05
93	fernandoylet	2	134	136	0.01
94	ramon2024	2	138	140	0.01
95	clarissaaaa	2	4	6	0.33
96	wittyzell	2	323	325	0.01
97	abhay2695	2	192	194	0.01
98	oldfool	1	50	51	0.02
99	hivewatchers	1	241	242	0
100	technicalside	1	417	418	0
101	adcreatordesign	1	659	660	0
102	silviamaria	1	24	25	0.04
103	meme.bot	1	6234	6235	0
104	tht	1	34	35	0.03
105	bigorna1	1	34	35	0.03
106	visionaer3003	1	0	1	1
107	mimtan	1	77	78	0.01
108	vragolana	1	79	80	0.01
109	cloverhive	1	7	8	0.13
110	wanderingmoon	1	85	86	0.01
111	mizuosemla	1	59	60	0.02
112	wengyyy	1	9	10	0.1
113	daltono	1	500	501	0
114	leogolj	1	56	57	0.02
115	neoxian-city	1	20	21	0.05
116	wilfredocav	1	189	190	0.01
117	discovery-it	1	733	734	0
118	manncpt	1	101	102	0.01
119	ptaku	1	12	13	0.08
120	cuddlekitten	1	357	358	0
121	rocinanteprimo	1	11	12	0.08
122	mipiano	1	626	627	0
123	redditposh	1	308	309	0
124	dimascastillo90	1	114	115	0.01
125	wayuu-reg	1	58	59	0.02
126	jijisaurart	1	286	287	0
127	sassy.cebuana	1	51	52	0.02
128	erikah	1	737	738	0
129	maccmacc	1	58	59	0.02
130	kerrislravenhill	1	511	512	0
131	josiebalderas	1	0	1	1
132	francyrios75	1	40	41	0.02
133	noemilunastorta	1	214	215	0
134	dodovietnam	1	117	118	0.01
135	lionsaturbix	1	76	77	0.01
136	ayane-chan	1	138	139	0.01
137	tragiclady	1	15	16	0.06
138	edebiyat	1	0	1	1
139	travelfeed	1	115	116	0.01
140	baycan	1	1	2	0.5
141	elwnyx	1	0	1	1
142	adysscheryl	1	416	417	0
143	emrebeyler	1	3	4	0.25
144	agmoore	1	225	226	0
145	ewkaw	1	227	228	0
146	aly.stor	1	24	25	0.04
147	sydechan	1	44	45	0.02
148	brataka	1	104	105	0.01
149	nasseir	1	22	23	0.04
150	naymhapz	1	547	548	0
151	not-here	1	14	15	0.07
152	lordshah	1	191	192	0.01
153	jhero22	1	114	115	0.01
154	mahmutsahintepee	1	0	1	1
155	melinda010100	1	630	631	0
156	proofofbrainblog	1	220	221	0
157	stefano.massari	1	446	447	0
158	sperosamuel15	1	1019	1020	0

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