SQL Script to get 2020 data from Hive Blockchain

in blurtindia •  4 years ago 

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.
  • 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


Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE BLURT!