SQL Script to get 2020 data from Hive Blockchain

avatar
(Edited)

Two days back I shared my 2020 data in a nice tabulation along with some fancy bar charts. It was an interesting activity to prepare scripts for the same and get the data from Hive SQL. As it is now available free of cost to the users, Hive SQL is easy and handy to get our own reports.

Yesterday after writing my article a few people came to my DM and asked me for the scripts to check their stats. I thought I would write an article and share the SQL script that I prepared to get the data for 2020.

I really wanted to write a series of articles to share the common scripts that would be useful for anyone using Hive SQL to grab some data from the blockchain. But that is going to be a separate task and for now, I would better share the script I used to get this simple data for 2020.

For those who are familiar with SQL scripts, this article would make some sense but for others, it shouldn't be a difficult task to get the data with the scripts. I use Heidi SQL to run the scrips and get the data from Hive SQL. But before that, you will need a subscription to Hive SQL to get the connection string.

Prerequisites

  • A little knowledge of SQL scripts.
  • Hive SQL subscription and connection string. This post will help you get it.
  • A SQL server client like Heidi SQL or SQL Server Management Studio or any preferable SQL client.

Hope all the above are ready and I would like to take you through the scripts directly.

Total Curation Rewards month-wise within a given date range

image.png

SELECT  DATEPART(month, timestamp) AS Month, 
        sum(reward) AS TotalReward,  
        cast((((SELECT total_vesting_fund_hive 
                FROM dbo.DynamicGlobalProperties WITH (NOLOCK)) * sum(reward)) / 
                (
                  SELECT total_vesting_shares 
                  FROM dbo.DynamicGlobalProperties WITH (NOLOCK))) as numeric(36,3)
                ) AS TotalHP
FROM "DBHive"."dbo"."VOCurationRewards"
WHERE curator = 'bala41288'  
AND timestamp >= '2020-01-01'
AND timestamp < '2020-12-31' 
GROUP BY DATEPART(month, TIMESTAMP)
ORDER BY MONTH ASC

Total Author Rewards month-wise within a given date range

image.png


SELECT DATEPART(month, timestamp) AS MONTH, 
         sum(hbd_payout) AS TotalHBDPayout, 
         sum(hive_payout) AS TotalHivePayout, 
         sum(vesting_payout) AS TotalVestingPayout, 
         cast((((SELECT total_vesting_fund_hive 
                    FROM dbo.DynamicGlobalProperties WITH (NOLOCK)) * sum(vesting_payout)) / 
                        (
                          SELECT total_vesting_shares 
                          FROM dbo.DynamicGlobalProperties WITH (NOLOCK))
                        ) as numeric(36,3)) AS TotalHP
FROM "DBHive"."dbo"."VOAuthorRewards"
WHERE author = 'bala41288'  
AND timestamp >= '2020-01-01'
AND timestamp < '2020-12-31' 
GROUP BY DATEPART(month, TIMESTAMP)
ORDER BY MONTH ASC

Total count of Posts month-wise within a given date range

image.png


SELECT DATEPART(month, created) AS Month, 
         count(permlink) AS TotalPosts       
FROM "DBHive"."dbo"."Comments"
WHERE author = 'bala41288'  
AND created >= '2020-01-01'
AND created <= '2020-12-31' 
AND parent_author = ''
GROUP BY DATEPART(month, created)
ORDER BY MONTH ASC

Total count of Comments month-wise within a given date range

image.png

SELECT DATEPART(month, created) AS Month, 
         count(permlink) AS TotalPosts       
FROM "DBHive"."dbo"."Comments"
WHERE author = 'bala41288'  
AND created >= '2020-01-01'
AND created <= '2020-12-31' 
AND parent_author = ''
GROUP BY DATEPART(month, created)
ORDER BY MONTH ASC

Hope the above scripts are useful. As I said, I will try to share similar scripts in the future. If you have any questions, please ask in the comments section.



Kani Bot notification service


image.png

image.png

image.png


Posted Using LeoFinance Beta



0
0
0.000
28 comments
avatar

pixresteemer_incognito_angel_mini.png
Bang, I did it again... I just rehived your post!
Week 40 of my contest just started...you can now check the winners of the previous week!
9

0
0
0.000
avatar

Do you know about any curation tools that would help me to find good content ? Sometimes back I think, there was one by @curie ?

0
0
0.000
avatar

Discovering good content is always a challenge because the new post page is not very useful. I use the Curator Picks tab if I use leofinance.io. Otherwise, you will have to rely only on your feed by following people you think to write good content regularly.

Yeah, I also remember there used to be one by @curie. Now after the communities got introduced, each community now have their own methods to discover good contents.

Posted Using LeoFinance Beta

0
0
0.000
avatar

I need to start playing around with Hive SQL

Posted Using LeoFinance Beta

0
0
0.000
avatar

Sure bro. It is not a big deal. If you need any help with any script/data do let me know. But it is really interesting to lookup data with Hive SQL. It is pretty handy. :) Cheers! !BEER

Posted Using LeoFinance Beta

0
0
0.000
avatar

Thank you very much for sharing this information
very useful.
Do you also have an ER diagram or some sort of documentation for different db tables ?

0
0
0.000
avatar

The table structures are not that complicated. Initially, I was in your position looking for schema details, and later after I started connecting, it was pretty much straightforward. If you connect through Heidi SQL, you will be able to see the tables by yourself and can start writing queries.

Posted Using LeoFinance Beta

0
0
0.000
avatar

Thanks bala.
look like I will like to play with this tool.

Posted Using LeoFinance Beta

0
0
0.000
avatar

Yeah cool. I think being a backend guy, you will find it very interesting after you start doing some queries. If you need any help do let me know. :) !wine !BEER

Posted Using LeoFinance Beta

0
0
0.000
avatar

thanks. i tuched SQL a bit but i don't really know where is what data located and how is it called, so this will help to learn a bit more.

0
0
0.000
avatar

Sorry, you don't have enough staked BEER in your account. You need 24 BEER in your virtual fridge to give some of your BEER to others. To view or trade BEER go to hive-engine.com

0
0
0.000