Exploring Hive Communities with HiveSQL

hivesql.png

One of the powerful feature of Hive is its Communities. Today, I decided to explore Hive Communities using HiveSQL. Nothing too complicated. To start, I wanted to know how many communities have been created so far and how many subscribers each has. I would like to share how to do that with HiveSQL. This can help later to make more interesting queries like how active each communities are, how many posts and comments every day, and also how are posts and comments are being rewarded.

There are three Communities related tables in HiveSQL. They are Communities, CommunitiesSubscribers, and CommunitiesRoles. Today I will be using first two. CommunitiesSubscribers has two kind of information, which are community - name of the community and subscriber - name of the subscriber. Communities table has more columns. They are about, description, flag_text, language, name, nsfw, title, TS, type. Most of them are self-explanatory. Now confusion may appear with name and title.

By Hive blockchain convention when communities are created they are named like 'hive-1234567'. This is stored in name column of Communities table. It is also stored in community column of CommunitiesSubscribers table. Now, the title column in Communities tables stores the name of the Community given by the creator of the community. For example, OCD, GEMS, LeoFinance, Curie, etc.

Using the following query we can get the total number of communities that have subscribers.

SELECT COUNT(DISTINCT community)
FROM CommunitiesSubscribers

The result will be 2150 communities.

To get the the list of the communities with the amount of subscribers we will need to join two tables - Communities and CommunitiesSubscribers. As usual I will be using the HiveSQL queries within a python script. This will make it easy to output the result with a table markdown. Below is the list top 500 Hive communities ordered by the amount of subscribers.

After the list, at the end of the post you can also see and review the python code. Feel free to use it in your own scripts to explore HiveSQL and let me know if you have questions or suggestions.


500 Hive Communities

#CommunitySubscribers
1GEMS8857
2OCD7784
3DTube7334
4Hive Gaming4137
5LeoFinance4056
6The LIFESTYLE LOUNGE3680
7Photography Lovers3454
8Photography3316
9OnChainArt3287
10Freewriters2781
11Natural Medicine2743
12Cervantes2736
13Sketchbook2598
14Actifit2558
15TravelFeed2545
16Foodies Bee Hive2505
17Project HOPE2294
18Black And White2265
19Threespeak2162
20Threespeak - OLD2139
21Amazing Nature2077
22Education2035
23Qurator1906
24BDCommunity1853
25DrugWars1831
26The City of Neoxian1732
27Music1537
28Splinterlands1429
29Haveyoubeenhere1397
30HiveVenezuela1384
31Alien Art Hive1348
32PhotoFeed1318
33StemSocial1304
34HiveDevs1292
35PHOTOGRAPHY [DAC]1291
36ecoTrain1280
37Movies & TV Shows1202
38Deep Dives1171
39The Ink Well1144
40Faces of Hive1115
41Palnet1067
42Fascinating Insects1054
43Sports Talk Social1031
44Ecency1016
45Discovery-it958
46Picture A Day925
47Feathered Friends886
48Build-It866
49D.Buzz859
50About PeakD859
51HeartChurch848
52SteemPeak845
53HIVE CN 中文社区835
54HODL787
55I Am Alive Challenge768
56Ask the Hive764
57Loving HIVE ❤757
58BEER725
59Deutsch D-A-CH715
60Funny708
61STEMGeeks695
62Urban Exploration691
63WeedCash Network665
64Abundance Tribe663
65Korea • 한국 • KR • KO645
66MakeUp Power640
67Ladies of Hive620
68Hive Statistics618
69Hive Governance595
70Curie568
71Let's Make a Collage560
72Christian Fellowship550
73SCT.암호화폐.Crypto548
74Blogging Challenge547
75hivemexico537
76EN ESPAÑOL527
77Feel Good526
78DIYHub523
79Stock Images519
80Fungi Lovers505
81Shadow Hunters504
82Wednesday Walk501
83NeedleWorkMonday495
84Inner Blocks486
85Portrait Photography485
86Star Wars464
87Live Hive Music454
88Original MEMEs453
89BIBLE READERS447
90DClub443
91R2cornell434
92Agricultural Mindset434
93HIVESICIANS431
94VIMM430
95DSound429
96Team Ukraine424
97Literatos422
98Market Friday422
99Always a Flower409
100Hive Pets407
101EXHAUST404
102Olio di Balena404
103Hive.IO Community402
104DLIKE388
105Lightpainters United387
106CCC387
107The CTP Swarm386
108TOP 👍 or 👎 FLOP378
109mini.topia371
110STEEM CN/中文367
111Hive Open Mic351
112Ulogs345
113COVID-19332
114Spanish City331
115Nerday329
116La radio321
117Pixelart308
118HiveHustlers307
119Creative Coin301
120Blockchain Gaming301
121The Man Cave298
122Coronavirus Pandemic297
123Silver Gold Stackers296
124Hive Naija280
125Engage the weekend279
126Hive Improvement277
127Motherhood272
128The Chess Community271
129Hive Cross Culture !268
130COMIC BOOKS fan/pro266
131The Anime Realm260
132SkateHive259
133dPoll250
134Old_Gems246
135TheTerminal245
136Stars242
137Polish HIVE239
138Love The Clouds239
139Community Schools238
140Self Improvement.237
141zzan237
142Hive Mobile Games236
143ColorChallenge229
144TripleA228
145DCooperation228
146del227
147Home Edders225
148Full Deportes223
149PeakMonsters222
150Communities Feedback219
151Contest Fun216
152ReggaeJAHM212
153Hive PH208
154Rising Star Game207
155LEGO205
156Photocircle202
157Bitcoin200
158GOAT.Gang198
159QUAC197
160Musicforlife 🎶193
161Top Family192
162HIVE-CON190
163The Talent Club187
164Hive Book Club185
165Team Malaysia185
166Hive Think Tank185
167PoliticallyIncorrect184
168Say Anything183
169INDONESIA182
170OpenSeed180
171SpiritWeb179
172My Picture Day177
173Conspiracy Realists176
174World News175
175Art174
176Poetic Universe174
177Philosophy172
178Club12172
179VDC170
180Programming170
181Dapps168
182Threespeak Team165
183Cryptobrewmaster165
184dPet163
185Cat Photos159
186Archon158
187Hive JP157
188Comics Games Cartoon155
189Alebrijes151
190Informationwar151
191The Pew151
192Mayvileros148
193Startups147
194Caturday145
195SteemPostItalia144
196Cryptocurrency143
197Crypt0's News!140
198Curangel140
199ChallengeEOS140
200Blockchain Wizardry138
201Atheism136
202Shady Writing133
203ENTROPÍA132
204Outdoors131
205Threeshorts130
206ASEAN Hive130
207Tommy Robinson130
208PhotoBomb Challenge130
209Hive Projects129
210Homesteading129
211Atom Collector128
212500 Words a day128
213DIGITAL MINE128
214Insect Pub127
215Cryptocurrency125
216HivePosh124
217MonsterMarket124
218AskMe124
219Hi From Hive123
220PEAK FAQ PROJECT119
221Toys on Hive118
222Rally118
223The Steem News117
224Die Kneipe117
225Hive SBI114
226Memehub112
227Economics111
228Planetauto110
229FreeSpeech109
230StreetArt106
231Hive Skills104
232Food Photography104
233Road Trips100
234AFuckedUpCommunity98
235Memechallenge97
236APPICS96
237World of Music94
238Collage94
239FullAlt92
240Porn92
241Manipulation Station92
242Writers Inc91
243Dance and music90
244Architecture+Design89
245Hive Marketing89
246PL-TravelFeed87
247Fundition86
248The Alliance86
249CLEAN PLANET85
250Česky!84
251La Ruche84
252EXODE Pilots83
253The Ramble81
254Girl Gone Crypto80
255Gaming Community80
256Classical Music78
257Poetry City77
258Sound Music77
259Telokanda76
260Hive Bangladesh75
261Venezuela74
262CoinGecko73
263BeYourself72
264Finally Friday72
265Hivetorch72
266Bulgaria71
267Meme70
268Python70
269News & Views70
270Excel For All70
271TeamPause69
272Hive Beginners Tips69
273Deutsch Community69
274Guitar68
275Hivemind67
276Birding / Wildlife67
277Digital Drawing66
278Team México66
279Politics66
280Steem-Bounty65
281Agora 한국정치와 국제정치 문제65
282The Anime Hive64
283Celfmagazine64
284DTIYS64
285Infowars63
286Music63
287Bitcoin MYK62
288Game Development62
289Travel Photography62
290Hive Austria Meetups62
291HiveDownvoteRewards61
292Community Announce61
293Closed59
294BreadBakers59
295On Impact59
296Citizen Journalists59
297GEEKZ59
298Succulent growers59
299HuaRen.News58
300CCH58
301TeamUK57
302Quello57
303Awakening Poetry56
304Macro56
305BRO SGN56
306SteemHelp55
307IndiaUnited55
308Astronomy54
309HiveBootcampHub54
310Utopian53
311Philippines53
312hive.argentina53
313Electronic Music53
314Kidz Community52
315Steemfest 452
316Original Music52
317Linux52
318GitPlait51
319KWH 한국전쟁사51
320Music Festivals 😎51
321HiveCats51
322Team Australia51
323Cook and bake51
324whalepower50
325GmbH50
326LearnHive50
327OpenSource48
328HiveSQL48
329Raspberry Pi48
330Black Sheep Bloggers48
331Why am I on Steem?48
332Worldbuilding48
333BlockChainPhoto48
334SP-GROUP48
335Travel48
336Tech Support47
337DCooperation in 3s47
338Draw A Day47
339SublimeSunday47
340SPinvest47
341QANON46
342HDS46
343HIVELIST Classifieds46
344Art46
345Running46
346Deutsch von A bis Z46
347Flowers Pictures45
348Crypto45
349DeFi Campus44
350Model-Agency44
351Talent Showcase44
352SoCal Steemit44
353Banjo43
354Heavy Metal43
355Crypto to Mainstream43
356CATSNAPS43
357Photography Lovers43
358Mene Maniacs43
359ActnEarn42
360HIVE APPS41
361Monochrom41
362ThreeD (3D) Printing41
363KoreanHistory 한국사41
364BitShares41
365Kittens41
366Nature41
367Bananafish40
368Video40
369Veterans40
370InfoTech40
371The Green Thumb39
372Eco Zone39
373c0ff33a Community39
374Black Lives Matter39
375Podcasts39
376Writers38
377YELLING38
378Be Awesome38
379Steem Portugal38
380Filmmakers37
381DeFi Universe37
382NewSteem37
383OldWorldBuilding37
384- Q -37
385Your Top 3 Contest36
386FAQ/HowTo for beempy36
387Cryptocurrency36
388Fiction and poetry35
389Hive Music Italia35
390NIGERIAN HIVE 🇳🇬35
391Alternative Weekend35
392Votovzla35
393MES Science35
394We Love Fractals35
395Technical Analysis35
396Okay Boomer35
397Garden35
398Pirate World35
399Develop Spanish35
400Anarchism35
401Learning Python34
402TRON Community34
403Templo34
404Deadpost Initiative34
405Dolphin Assemble34
406Science Fiction34
407Murphy's Bar34
408Luke Is Alive33
409Food33
410Sharing & Earning33
411Giveaways33
412Steem Sister Show33
413Nug Porn Cannabis33
414JTS Global Community33
415BCM 몰 공지사항33
416Graphic Design33
417Hive Sports33
418LandscapePhotography32
419INDONESIAN HIVE32
420CWU RECIPE BOOK32
421HiveStreet32
422World Of Art32
423CATS32
424Polska Spolecznosc32
425Crypto Talk32
426community Leaders32
427SEAPHOTOGRAPHY32
428Hive Affiliates32
429Team Serbia32
430HIVE-MYANMAR31
431BW Nude Art31
432SaturdayMemories31
433SteemItQA Community31
434Creating Comics31
435API TESTING31
436Canna31
437Books31
438Korean Humor Untold31
439Street Art31
440steem-games31
441Francophonie30
442The Hood30
443EDM30
444Folwark30
445This Is Awesome30
446HiveFest30
447Dogs30
448ecoVillages29
449Koinos29
450Cryptoversity29
451EmprendeOnlineVzla29
452JAHMZ29
453Hivecannabis29
454Introduce Yourself29
455Gridcoin (GRC)29
456ArtBees29
457[DE] HIVE FAQ German29
458Digital Marketing28
459Blockchain Poets28
460Food of the World28
461BBQ & Drinks and Fun28
462Telos28
463Gaming28
464Rice-illicious! S128
465Friends28
466SteemCity28
467Netflix & Streaming27
468The SoCal Hive27
469Meetups27
470Españoles en Steemit27
471CyberPunk27
472Hive USA27
473Perfspots27
474South Australia27
475Book it Suda27
476Crypto Trading Guru26
477Hive English Club26
478Cryptocurrency26
479poetry26
480DIY26
481Alexandria's Library26
482tipU Service26
483Travel26
484Sports Betting26
485OneLoveDTube26
486chinese26
487Deutschländer26
488Food&Travel 음식과 여행26
489Introduce Yourself26
490Dash26
491Board Gaming25
492DPorn25
493Charity25
494No Agenda Producers25
495CyberSecurity25
496Pilipino25
497Photography25
498Islam News25
499BattleGames25
500Animals24

Python/SQL code to get the list above

import pyodbc

connection = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                            'Server=vip.hivesql.io;'
                            'Database=DBHive;'
                            'uid=Hive-geekgirl;'
                            'pwd=XXXXXXXXXXXXXXX')
cursor = connection.cursor()

SQLCommand = '''
SELECT name, title, COUNT(subscriber)
FROM Communities
JOIN CommunitiesSubscribers
ON Communities.name = CommunitiesSubscribers.community
GROUP BY name, title
ORDER BY COUNT(subscriber) DESC
'''

result = cursor.execute(SQLCommand)
result = result.fetchmany(500)
connection.close()

text = '| # | Community | Subscribers | \n| ----------- | ----------- | ----------- | \n'
count = 1
for community in result:
    text_line = '|' + str(count) +'| [' + community[1]
    text_line += '](https://hive.blog/trending/' + community[0] 
    text_line += ') |' + str(community[2]) + ' | \n'
    text = text + text_line
    count += 1

print(text)

Posted Using LeoFinance Beta



0
0
0.000
11 comments
avatar

I'm pretty sure that dtube used account's keys to self-subscribe.

Kinda lame that there's two photography communities so close to one another.

0
0
0.000
avatar

I enjoy both communities and find that they trend different things.

0
0
0.000
avatar

The code is very well written. I don't think we can make it more efficient in any way.

I have downloaded the 2nd layer transaction details from Jan 1 to Jan 25 and stored it in 'Shelve' . Let me know if you want to make use of it .

!WINE

Posted Using LeoFinance Beta

0
0
0.000
avatar

GEMS no surprise the shit post community lol

Pretty surprised about the lifestyle and gaming one didn't know they were that popular, you can see how content is rather concentrated on rewards rather than categories though

0
0
0.000
avatar

Wowwww, that is very interesting and helpful! tHanK yoU!

0
0
0.000
avatar

Great Post. I'll be using HiveSQL. I'm glad to see Silver Gold Stackers in the list.

0
0
0.000