Exploring Hive Communities with HiveSQL

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.

| # | Community | Subscribers | | ----------- | ----------- | ----------- | |1| GEMS |8857 | |2| OCD |7784 | |3| DTube |7334 | |4| Hive Gaming |4137 | |5| LeoFinance |4056 | |6| The LIFESTYLE LOUNGE |3680 | |7| Photography Lovers |3454 | |8| Photography |3316 | |9| OnChainArt |3287 | |10| Freewriters |2781 | |11| Natural Medicine |2743 | |12| Cervantes |2736 | |13| Sketchbook |2598 | |14| Actifit |2558 | |15| TravelFeed |2545 | |16| Foodies Bee Hive |2505 | |17| Project HOPE |2294 | |18| Black And White |2265 | |19| Threespeak |2162 | |20| Threespeak - OLD |2139 | |21| Amazing Nature |2077 | |22| Education |2035 | |23| Qurator |1906 | |24| BDCommunity |1853 | |25| DrugWars |1831 | |26| The City of Neoxian |1732 | |27| Music |1537 | |28| Splinterlands |1429 | |29| Haveyoubeenhere |1397 | |30| HiveVenezuela |1384 | |31| Alien Art Hive |1348 | |32| PhotoFeed |1318 | |33| StemSocial |1304 | |34| HiveDevs |1292 | |35| PHOTOGRAPHY [DAC] |1291 | |36| ecoTrain |1280 | |37| Movies & TV Shows |1202 | |38| Deep Dives |1171 | |39| The Ink Well |1144 | |40| Faces of Hive |1115 | |41| Palnet |1067 | |42| Fascinating Insects |1054 | |43| Sports Talk Social |1031 | |44| Ecency |1016 | |45| Discovery-it |958 | |46| Picture A Day |925 | |47| Feathered Friends |886 | |48| Build-It |866 | |49| D.Buzz |859 | |50| About PeakD |859 | |51| HeartChurch |848 | |52| SteemPeak |845 | |53| HIVE CN 中文社区 |835 | |54| HODL |787 | |55| I Am Alive Challenge |768 | |56| Ask the Hive |764 | |57| Loving HIVE ❤ |757 | |58| BEER |725 | |59| Deutsch D-A-CH |715 | |60| Funny |708 | |61| STEMGeeks |695 | |62| Urban Exploration |691 | |63| WeedCash Network |665 | |64| Abundance Tribe |663 | |65| Korea • 한국 • KR • KO |645 | |66| MakeUp Power |640 | |67| Ladies of Hive |620 | |68| Hive Statistics |618 | |69| Hive Governance |595 | |70| Curie |568 | |71| Let's Make a Collage |560 | |72| Christian Fellowship |550 | |73| SCT.암호화폐.Crypto |548 | |74| Blogging Challenge |547 | |75| hivemexico |537 | |76| EN ESPAÑOL |527 | |77| Feel Good |526 | |78| DIYHub |523 | |79| Stock Images |519 | |80| Fungi Lovers |505 | |81| Shadow Hunters |504 | |82| Wednesday Walk |501 | |83| NeedleWorkMonday |495 | |84| Inner Blocks |486 | |85| Portrait Photography |485 | |86| Star Wars |464 | |87| Live Hive Music |454 | |88| Original MEMEs |453 | |89| BIBLE READERS |447 | |90| DClub |443 | |91| R2cornell |434 | |92| Agricultural Mindset |434 | |93| HIVESICIANS |431 | |94| VIMM |430 | |95| DSound |429 | |96| Team Ukraine |424 | |97| Literatos |422 | |98| Market Friday |422 | |99| Always a Flower |409 | |100| Hive Pets |407 | |101| EXHAUST |404 | |102| Olio di Balena |404 | |103| Hive.IO Community |402 | |104| DLIKE |388 | |105| Lightpainters United |387 | |106| CCC |387 | |107| The CTP Swarm |386 | |108| TOP 👍 or 👎 FLOP |378 | |109| mini.topia |371 | |110| STEEM CN/中文 |367 | |111| Hive Open Mic |351 | |112| Ulogs |345 | |113| COVID-19 |332 | |114| Spanish City |331 | |115| Nerday |329 | |116| La radio |321 | |117| Pixelart |308 | |118| HiveHustlers |307 | |119| Creative Coin |301 | |120| Blockchain Gaming |301 | |121| The Man Cave |298 | |122| Coronavirus Pandemic |297 | |123| Silver Gold Stackers |296 | |124| Hive Naija |280 | |125| Engage the weekend |279 | |126| Hive Improvement |277 | |127| Motherhood |272 | |128| The Chess Community |271 | |129| Hive Cross Culture ! |268 | |130| COMIC BOOKS fan/pro |266 | |131| The Anime Realm |260 | |132| SkateHive |259 | |133| dPoll |250 | |134| Old_Gems |246 | |135| TheTerminal |245 | |136| Stars |242 | |137| Polish HIVE |239 | |138| Love The Clouds |239 | |139| Community Schools |238 | |140| Self Improvement. |237 | |141| zzan |237 | |142| Hive Mobile Games |236 | |143| ColorChallenge |229 | |144| TripleA |228 | |145| DCooperation |228 | |146| del |227 | |147| Home Edders |225 | |148| Full Deportes |223 | |149| PeakMonsters |222 | |150| Communities Feedback |219 | |151| Contest Fun |216 | |152| ReggaeJAHM |212 | |153| Hive PH |208 | |154| Rising Star Game |207 | |155| LEGO |205 | |156| Photocircle |202 | |157| Bitcoin |200 | |158| GOAT.Gang |198 | |159| QUAC |197 | |160| Musicforlife 🎶 |193 | |161| Top Family |192 | |162| HIVE-CON |190 | |163| The Talent Club |187 | |164| Hive Book Club |185 | |165| Team Malaysia |185 | |166| Hive Think Tank |185 | |167| PoliticallyIncorrect |184 | |168| Say Anything |183 | |169| INDONESIA |182 | |170| OpenSeed |180 | |171| SpiritWeb |179 | |172| My Picture Day |177 | |173| Conspiracy Realists |176 | |174| World News |175 | |175| Art |174 | |176| Poetic Universe |174 | |177| Philosophy |172 | |178| Club12 |172 | |179| VDC |170 | |180| Programming |170 | |181| Dapps |168 | |182| Threespeak Team |165 | |183| Cryptobrewmaster |165 | |184| dPet |163 | |185| Cat Photos |159 | |186| Archon |158 | |187| Hive JP |157 | |188| Comics Games Cartoon |155 | |189| Alebrijes |151 | |190| Informationwar |151 | |191| The Pew |151 | |192| Mayvileros |148 | |193| Startups |147 | |194| Caturday |145 | |195| SteemPostItalia |144 | |196| Cryptocurrency |143 | |197| Crypt0's News! |140 | |198| Curangel |140 | |199| ChallengeEOS |140 | |200| Blockchain Wizardry |138 | |201| Atheism |136 | |202| Shady Writing |133 | |203| ENTROPÍA |132 | |204| Outdoors |131 | |205| Threeshorts |130 | |206| ASEAN Hive |130 | |207| Tommy Robinson |130 | |208| PhotoBomb Challenge |130 | |209| Hive Projects |129 | |210| Homesteading |129 | |211| Atom Collector |128 | |212| 500 Words a day |128 | |213| DIGITAL MINE |128 | |214| Insect Pub |127 | |215| Cryptocurrency |125 | |216| HivePosh |124 | |217| MonsterMarket |124 | |218| AskMe |124 | |219| Hi From Hive |123 | |220| PEAK FAQ PROJECT |119 | |221| Toys on Hive |118 | |222| Rally |118 | |223| The Steem News |117 | |224| Die Kneipe |117 | |225| Hive SBI |114 | |226| Memehub |112 | |227| Economics |111 | |228| Planetauto |110 | |229| FreeSpeech |109 | |230| StreetArt |106 | |231| Hive Skills |104 | |232| Food Photography |104 | |233| Road Trips |100 | |234| AFuckedUpCommunity |98 | |235| Memechallenge |97 | |236| APPICS |96 | |237| World of Music |94 | |238| Collage |94 | |239| FullAlt |92 | |240| Porn |92 | |241| Manipulation Station |92 | |242| Writers Inc |91 | |243| Dance and music |90 | |244| Architecture+Design |89 | |245| Hive Marketing |89 | |246| PL-TravelFeed |87 | |247| Fundition |86 | |248| The Alliance |86 | |249| CLEAN PLANET |85 | |250| Česky! |84 | |251| La Ruche |84 | |252| EXODE Pilots |83 | |253| The Ramble |81 | |254| Girl Gone Crypto |80 | |255| Gaming Community |80 | |256| Classical Music |78 | |257| Poetry City |77 | |258| Sound Music |77 | |259| Telokanda |76 | |260| Hive Bangladesh |75 | |261| Venezuela |74 | |262| CoinGecko |73 | |263| BeYourself |72 | |264| Finally Friday |72 | |265| Hivetorch |72 | |266| Bulgaria |71 | |267| Meme |70 | |268| Python |70 | |269| News & Views |70 | |270| Excel For All |70 | |271| TeamPause |69 | |272| Hive Beginners Tips |69 | |273| Deutsch Community |69 | |274| Guitar |68 | |275| Hivemind |67 | |276| Birding / Wildlife |67 | |277| Digital Drawing |66 | |278| Team México |66 | |279| Politics |66 | |280| Steem-Bounty |65 | |281| Agora 한국정치와 국제정치 문제 |65 | |282| The Anime Hive |64 | |283| Celfmagazine |64 | |284| DTIYS |64 | |285| Infowars |63 | |286| Music |63 | |287| Bitcoin MYK |62 | |288| Game Development |62 | |289| Travel Photography |62 | |290| Hive Austria Meetups |62 | |291| HiveDownvoteRewards |61 | |292| Community Announce |61 | |293| Closed |59 | |294| BreadBakers |59 | |295| On Impact |59 | |296| Citizen Journalists |59 | |297| GEEKZ |59 | |298| Succulent growers |59 | |299| HuaRen.News |58 | |300| CCH |58 | |301| TeamUK |57 | |302| Quello |57 | |303| Awakening Poetry |56 | |304| Macro |56 | |305| BRO SGN |56 | |306| SteemHelp |55 | |307| IndiaUnited |55 | |308| Astronomy |54 | |309| HiveBootcampHub |54 | |310| Utopian |53 | |311| Philippines |53 | |312| hive.argentina |53 | |313| Electronic Music |53 | |314| Kidz Community |52 | |315| Steemfest 4 |52 | |316| Original Music |52 | |317| Linux |52 | |318| GitPlait |51 | |319| KWH 한국전쟁사 |51 | |320| Music Festivals 😎 |51 | |321| HiveCats |51 | |322| Team Australia |51 | |323| Cook and bake |51 | |324| whalepower |50 | |325| GmbH |50 | |326| LearnHive |50 | |327| OpenSource |48 | |328| HiveSQL |48 | |329| Raspberry Pi |48 | |330| Black Sheep Bloggers |48 | |331| Why am I on Steem? |48 | |332| Worldbuilding |48 | |333| BlockChainPhoto |48 | |334| SP-GROUP |48 | |335| Travel |48 | |336| Tech Support |47 | |337| DCooperation in 3s |47 | |338| Draw A Day |47 | |339| SublimeSunday |47 | |340| SPinvest |47 | |341| QANON |46 | |342| HDS |46 | |343| HIVELIST Classifieds |46 | |344| Art |46 | |345| Running |46 | |346| Deutsch von A bis Z |46 | |347| Flowers Pictures |45 | |348| Crypto |45 | |349| DeFi Campus |44 | |350| Model-Agency |44 | |351| Talent Showcase |44 | |352| SoCal Steemit |44 | |353| Banjo |43 | |354| Heavy Metal |43 | |355| Crypto to Mainstream |43 | |356| CATSNAPS |43 | |357| Photography Lovers |43 | |358| Mene Maniacs |43 | |359| ActnEarn |42 | |360| HIVE APPS |41 | |361| Monochrom |41 | |362| ThreeD (3D) Printing |41 | |363| KoreanHistory 한국사 |41 | |364| BitShares |41 | |365| Kittens |41 | |366| Nature |41 | |367| Bananafish |40 | |368| Video |40 | |369| Veterans |40 | |370| InfoTech |40 | |371| The Green Thumb |39 | |372| Eco Zone |39 | |373| c0ff33a Community |39 | |374| Black Lives Matter |39 | |375| Podcasts |39 | |376| Writers |38 | |377| YELLING |38 | |378| Be Awesome |38 | |379| Steem Portugal |38 | |380| Filmmakers |37 | |381| DeFi Universe |37 | |382| NewSteem |37 | |383| OldWorldBuilding |37 | |384| - Q - |37 | |385| Your Top 3 Contest |36 | |386| FAQ/HowTo for beempy |36 | |387| Cryptocurrency |36 | |388| Fiction and poetry |35 | |389| Hive Music Italia |35 | |390| NIGERIAN HIVE 🇳🇬 |35 | |391| Alternative Weekend |35 | |392| Votovzla |35 | |393| MES Science |35 | |394| We Love Fractals |35 | |395| Technical Analysis |35 | |396| Okay Boomer |35 | |397| Garden |35 | |398| Pirate World |35 | |399| Develop Spanish |35 | |400| Anarchism |35 | |401| Learning Python |34 | |402| TRON Community |34 | |403| Templo |34 | |404| Deadpost Initiative |34 | |405| Dolphin Assemble |34 | |406| Science Fiction |34 | |407| Murphy's Bar |34 | |408| Luke Is Alive |33 | |409| Food |33 | |410| Sharing & Earning |33 | |411| Giveaways |33 | |412| Steem Sister Show |33 | |413| Nug Porn Cannabis |33 | |414| JTS Global Community |33 | |415| BCM 몰 공지사항 |33 | |416| Graphic Design |33 | |417| Hive Sports |33 | |418| LandscapePhotography |32 | |419| INDONESIAN HIVE |32 | |420| CWU RECIPE BOOK |32 | |421| HiveStreet |32 | |422| World Of Art |32 | |423| CATS |32 | |424| Polska Spolecznosc |32 | |425| Crypto Talk |32 | |426| community Leaders |32 | |427| SEAPHOTOGRAPHY |32 | |428| Hive Affiliates |32 | |429| Team Serbia |32 | |430| HIVE-MYANMAR |31 | |431| BW Nude Art |31 | |432| SaturdayMemories |31 | |433| SteemItQA Community |31 | |434| Creating Comics |31 | |435| API TESTING |31 | |436| Canna |31 | |437| Books |31 | |438| Korean Humor Untold |31 | |439| Street Art |31 | |440| steem-games |31 | |441| Francophonie |30 | |442| The Hood |30 | |443| EDM |30 | |444| Folwark |30 | |445| This Is Awesome |30 | |446| HiveFest |30 | |447| Dogs |30 | |448| ecoVillages |29 | |449| Koinos |29 | |450| Cryptoversity |29 | |451| EmprendeOnlineVzla |29 | |452| JAHMZ |29 | |453| Hivecannabis |29 | |454| Introduce Yourself |29 | |455| Gridcoin (GRC) |29 | |456| ArtBees |29 | |457| [DE] HIVE FAQ German |29 | |458| Digital Marketing |28 | |459| Blockchain Poets |28 | |460| Food of the World |28 | |461| BBQ & Drinks and Fun |28 | |462| Telos |28 | |463| Gaming |28 | |464| Rice-illicious! S1 |28 | |465| Friends |28 | |466| SteemCity |28 | |467| Netflix & Streaming |27 | |468| The SoCal Hive |27 | |469| Meetups |27 | |470| Españoles en Steemit |27 | |471| CyberPunk |27 | |472| Hive USA |27 | |473| Perfspots |27 | |474| South Australia |27 | |475| Book it Suda |27 | |476| Crypto Trading Guru |26 | |477| Hive English Club |26 | |478| Cryptocurrency |26 | |479| poetry |26 | |480| DIY |26 | |481| Alexandria's Library |26 | |482| tipU Service |26 | |483| Travel |26 | |484| Sports Betting |26 | |485| OneLoveDTube |26 | |486| chinese |26 | |487| Deutschländer |26 | |488| Food&Travel 음식과 여행 |26 | |489| Introduce Yourself |26 | |490| Dash |26 | |491| Board Gaming |25 | |492| DPorn |25 | |493| Charity |25 | |494| No Agenda Producers |25 | |495| CyberSecurity |25 | |496| Pilipino |25 | |497| Photography |25 | |498| Islam News |25 | |499| BattleGames |25 | |500| Animals |24 |

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