![](https://cdn.steemitimages.com/DQmVrtViVTarPui4ySKgwdVbm7k2wDmhsmGnaT5KH9cfQYr/image.png)

I installed my own version of [Hivemind](https://github.com/steemit/hivemind) so that I can play around with accessing some of the blockchain using Postgres. It took a while to sync. But once it did, *oh nelly!!* What is Hivemind? The repo says: > **Developer-friendly microservice powering social networks on the Steem blockchain.** > > Hive is a "consensus interpretation" layer for the Steem blockchain, maintaining the state of social features such as post feeds, follows, and communities. Written in Python, it synchronizes an SQL database with chain state, providing developers with a more flexible/extensible alternative to the raw steemd API. This means that you can bypass `steemd` and access data in a more traditional way. Often, business solutions use SQL for data. But you can't use SQL on `steemd`. So Hivemind solves that problem. Now, in reality, there's another goal to Hivemind, as mentioned in the @steemitblog application team update: [Hivemind/Communities, Sign-Ups, Developer Tools, Condenser, and More!](https://steemit.com/steemit/@steemitblog/applications-team-update-hivemind-communities-sign-ups-developer-tools-condenser-and-more): > Work on Hivemind 1.0 remains a major focus. Over the past few weeks, we have been heavily testing compatibility between hive and condenser. We have also committed significant resources to documenting the steps required for Hivemind integration, which will help community developers deploy and take full advantage of hive once it is ready. Hivemind will facilitate community front-ends. But the 1.0 version doesn't offer much in new features. It takes some of the load off of `steemd`, which is a *great* feature that I'm very excited about. But it's hard to convey why this is exciting. It's just a drop-in replacement for something that already works, right? Yes, but in doing so, it takes some of the load off of `steemd`. And that's a very good thing. At it's core, it does that by making it easier to query the same data. But in addition to that purpose, if you can run your own Postgres database, you can do some interesting queries. For example, I want to know what the top 10 apps are, by payout (all time). Well, the query for that looks like this: ```sql SELECT json::json->>'app', Sum(payout) FROM hive_posts_cache GROUP BY json::json->>'app' LIMIT 10; ``` And that gives us the following results: | App | Payout in SBD| |-------|-------| | steemit/0.1 | 1225887.103 | | busy/2.5.3 | 93830.625 | | dlive/0.1 | 86517.867 | | busy/2.5.4 | 63678.215 | | dtube/0.7 | 55127.314 | | busy/2.5.2 | 42774.839 | | *unknown* | 38544.404 | | steemhunt/1.0.0 | 34304.329 | | esteem/1.6.0 | 31151.406 | | dsound/0.3 | 17792.419 | Kinda cool, right? Or, I can query for specific mentions with certain tags: ```sql SELECT hive_posts.* FROM hive_posts WHERE ( hive_posts.id ) IN ( SELECT hive_posts_cache.post_id FROM hive_posts_cache WHERE ( hive_posts_cache.body LIKE '%@inertia%' ) AND ( hive_posts_cache.body LIKE '%@whatsup%' )) AND ( hive_posts.id ) IN ( SELECT hive_post_tags.post_id FROM hive_post_tags WHERE ( hive_post_tags.tag ) IN ( 'community', 'shoutout' )); ``` That one's saying that each result must have two mentions and either tag, which gives us the following results: [@steemexperience/update-on-the-steem-experience](/@steemexperience/update-on-the-steem-experience) [@wishmaiden/attention-noobs-come-join-the-voice-chat-community-at-steemspeak-com](/@wishmaiden/attention-noobs-come-join-the-voice-chat-community-at-steemspeak-com) [@arsenal49/400-followers-steemspeak-randowhale](/@arsenal49/400-followers-steemspeak-randowhale) [@vocalists-trail/thursday-shoutout](/@vocalists-trail/thursday-shoutout) [@steemexperience/3uaqax-update-on-the-steem-experience](/@steemexperience/3uaqax-update-on-the-steem-experience) [@steemexperience/45ncqm-update-on-the-steem-experience](/@steemexperience/45ncqm-update-on-the-steem-experience) Or, lets say we want to query posts that must have all three tags: `kitty` `pet` and `cute`: ```sql SELECT hive_posts.* FROM hive_posts WHERE ( hive_posts.id ) IN ( SELECT hive_post_tags.post_id FROM hive_post_tags WHERE hive_post_tags.tag = 'kitty') AND ( hive_posts.id ) IN ( SELECT hive_post_tags.post_id FROM hive_post_tags WHERE hive_post_tags.tag = 'pet') AND ( hive_posts.id ) IN ( SELECT hive_post_tags.post_id FROM hive_post_tags WHERE hive_post_tags.tag = 'cute'); ``` I'm really excited about this kind of query because normally, if we don't use SQL to do this kind of query, we get a huge result. For example, you might want all posts with `kitty` plus all posts with `pet` plus all posts with `cute` which would give you 23,844 results. But because I require all three tags in the result, I only get two: [@seoya/my-lovely-kitty-jelly](/@seoya/my-lovely-kitty-jelly) [@justwatchout/8nin82lj](/@justwatchout/8nin82lj) You can also ask for the most upvoted post (at this very moment): ```sql SELECT hive_posts.* FROM hive_posts INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id ORDER BY hive_posts_cache.rshares DESC LIMIT 1; ``` ... which is this: [@chbartist/right-before-the-daw](/@chbartist/right-before-the-daw) ... and the most downvoted post: ```sql SELECT hive_posts.* FROM hive_posts INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id ORDER BY hive_posts_cache.rshares ASC LIMIT 1; ``` ... which is this: [@joanaltres/re-elfspice-dan-larimer-so-insecure-he-has-to-self-vote-to-put-his-posts-that-already-are-getting-enough-votes-to-the-top-of-trending-20170802t061503811z](/@joanaltres/re-elfspice-dan-larimer-so-insecure-he-has-to-self-vote-to-put-his-posts-that-already-are-getting-enough-votes-to-the-top-of-trending-20170802t061503811z) Notice that the *highest upvoted* post is distinct from the *highest paid* post. This is because the market prices are a factor, as well as quadratic rewards, and the fact that this payout pre-dated the voting slider. ```sql SELECT hive_posts.* FROM hive_posts INNER JOIN hive_posts_cache ON hive_posts_cache.post_id = hive_posts.id ORDER BY hive_posts_cache.payout DESC LIMIT 1; ``` ... which is this: [@xeroc/piston-web-first-open-source-steem-gui---searching-for-alpha-testers](/@xeroc/piston-web-first-open-source-steem-gui---searching-for-alpha-testers) So yeah, I'm excited about Hivemind. It's a great way to look at the blockchain from a community perspective. **Bonus Query:** Here are my 10 most upvoted (ordered by `rshares`): [@inertia/deer-on-the-dock](/@inertia/deer-on-the-dock) [@inertia/primer-primer](/@inertia/primer-primer) [@inertia/ganymede-a-growing-collection-of-steem-web-tools](/@inertia/ganymede-a-growing-collection-of-steem-web-tools) [@inertia/creating-demand-for-steem-power-vote-negation](/@inertia/creating-demand-for-steem-power-vote-negation) [@inertia/prisma-pumpkin-patch](/@inertia/prisma-pumpkin-patch) [@inertia/dr-otto-vote-bidding-bot](/@inertia/dr-otto-vote-bidding-bot) [@inertia/radiator-0-3-4](/@inertia/radiator-0-3-4) [@inertia/before-and-after](/@inertia/before-and-after) [@inertia/profile](/@inertia/profile) [@inertia/steemit-the-blockchain](/@inertia/steemit-the-blockchain) And my 10 most downvoted: [@inertia/re-dantheman-origin-of-the-right-to-vote-and-how-the-system-denies-this-right-20160813t161354289z](/@inertia/re-dantheman-origin-of-the-right-to-vote-and-how-the-system-denies-this-right-20160813t161354289z) [@inertia/re-berniesanders-why-did-ned-lie-to-the-steem-community-20170315t004719152z](/@inertia/re-berniesanders-why-did-ned-lie-to-the-steem-community-20170315t004719152z) [@inertia/re-fyrstikken-to-the-co-owners-of-steem-i-am-being-stalked-and-flagged-by-bernieslanders-nextgencraphole-every-post-i-make-on-steem-20170406t055739063z](/@inertia/re-fyrstikken-to-the-co-owners-of-steem-i-am-being-stalked-and-flagged-by-bernieslanders-nextgencraphole-every-post-i-make-on-steem-20170406t055739063z) [@inertia/re-ats-david-re-inertia-re-jerrybanfield-i-am-sorry-for-my-last-post-20171010t180440642z](/@inertia/re-ats-david-re-inertia-re-jerrybanfield-i-am-sorry-for-my-last-post-20171010t180440642z) [@inertia/them-slashdot-trolls](/@inertia/them-slashdot-trolls) [@inertia/them-java-coders](/@inertia/them-java-coders) [@inertia/re-berniesanders-re-inertia-re-berniesanders-why-did-ned-lie-to-the-steem-community-20170315t010729361z](/@inertia/re-berniesanders-re-inertia-re-berniesanders-why-did-ned-lie-to-the-steem-community-20170315t010729361z) [@inertia/re-cryptopian68-re-inertia-re-haejin-v4dxybd8-20180205t042726761z](/@inertia/re-cryptopian68-re-inertia-re-haejin-v4dxybd8-20180205t042726761z) [@inertia/re-berniesanders-berniesanders-re-inertiare-haejin-v4dxybd8-20180204t235618208z](/@inertia/re-berniesanders-berniesanders-re-inertiare-haejin-v4dxybd8-20180204t235618208z) [@inertia/re-ats-witness-long-time-user-first-time-witness-20171121t175253701z](/@inertia/re-ats-witness-long-time-user-first-time-witness-20171121t175253701z)