Posts

SQL Script to get 2020 data from Hive Blockchain

avatar of @bala41288
25
Bala
@bala41288
·
·
0 views
·
3 min read

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

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

 
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

 
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

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.

Posted Using LeoFinance Beta