SQL Script to get 2020 data from Hive Blockchain

in LeoFinance2 months ago (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

Sort:  

Thanks for this. I hope I can find some time to five this a go. 😊

Posted Using LeoFinance Beta

Yeah sure. Once you get started, you will find this very interesting. :) Cheers!

Posted Using LeoFinance Beta

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.

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

Posted Using LeoFinance Beta

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


Cheers, @bala41288 You Successfully Shared 0.100 WINE With @r1s2g3.
You Earned 0.100 WINE As Curation Reward.
You Utilized 1/3 Successful Calls.

wine-greeting


WINE Current Market Price : 1.100 HIVE


Hey @r1s2g3, here is a little bit of BEER from @bala41288 for you. Enjoy it!

Learn how to earn FREE BEER each day by staking your BEER.

These are great!

Thanks buddy. :)

Posted Using LeoFinance Beta

hi @bala41288, thanks for sharing the sql scripts

One question, How do i get the Hive SQL subscription and connection string?

thanks
farhad kias
[imfarhad]

Hello @imfarhad. Thanks for the comments. This post will help you through the process.

https://peakd.com/hive-139531/@arcange/hivesql-free

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

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

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 ?

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

That's very useful thanks!

@tipu curate

Thanks for the tips. You are welcome. :)

Posted Using LeoFinance Beta

I need to start playing around with Hive SQL

Posted Using LeoFinance Beta

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

Me too! Might be handy since I'm in the process of trying to get some records together for the tax man. 😊

Posted Using LeoFinance Beta

Good luck dear.

Posted Using LeoFinance Beta


Hey @nathanmars, here is a little bit of BEER from @bala41288 for you. Enjoy it!

Learn how to earn FREE BEER each day by staking your BEER.

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

Very helpful thanks for sharing...
!BEER

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

Thanks for this! I am learning.

Posted Using LeoFinance Beta