Fetching Post and Comment Distribution of Accounts [January]

[kedi]

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

Comments

[poshtoken]

_{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.}

[flsfserkan]

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

[fernandoylet]

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

[kedi]

Exactly what I am trying to convey

[rsntpl]

thanks for this work , good luck 👏

[ahmetay]

Health to your hands. Nice work. Thanks.

[shakavon]

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 😸

[kedi]

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 🤠

[shakavon]

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.

[mehmetfix]

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

[kedi]

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

[mehmetfix]

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

Posted via D.Buzz

[kedi]

Yaser#5143

[mehmetfix]

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

Posted via D.Buzz

[tagmout]

Good evening, why I am here??

[tragiclady]

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

[tagmout]

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

[tragiclady]

That is weird, why though?

[tagmout]

Because I am not Turkish,

[bigorna1]

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

[kedi]

Sure, all mentions removed

[bigorna1]

Thank you.

[clarissaaaa]

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

[lordshah]

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

[wayuu-reg]

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!

[naymhapz]

Thank you for the mention. Much appreciated.

[missleray]

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

[hivebuzz]

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

[elwnyx]

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