Two days back I shared [my 2020 data](https://leofinance.io/@bala41288/2020-a-small-recap-as-an-author-and-a-curator) 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](https://peakd.com/hive-139531/@arcange/hivesql-free) 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](https://peakd.com/hive-139531/@arcange/hivesql-free) 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](https://images.hive.blog/DQmRtXCvWCiS8bPxmtEFC97Q6BCSX5KGM1nhK8bVD5jZXph/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](https://images.hive.blog/DQmP2VMw5ZvhXz7yRcwcBbxpoRrTk8GiZQ3qupXP9HjYY6B/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](https://images.hive.blog/DQmWPqQTCoSFGdfHX1eApAhNoWVVDZppgA3PKjcdn7BEQcv/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](https://images.hive.blog/DQmUdxupVfp8dcLvjcHRwNacBwkbobNtmj8Abfa63LhmNvo/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. 

---
---

<center>

[![Kani Bot notification service](https://files.peakd.com/file/peakd-hive/bala41288/DDxrJxFV-kani-banner-15fps.gif)](https://discord.com/invite/S4x6PFz)

[
![image.png](https://files.peakd.com/file/peakd-hive/bala41288/46eaz12N-image.png)](https://discord.gg/bGmS2tE)

[![image.png](https://images.hive.blog/DQmag62WZkMSrJAx5bupB6C8QSTdzQfZoCMsk5dp5kEjn1v/image.png)](https://r.honeygain.me/BALA4F8CD5)

[![image.png](https://files.peakd.com/file/peakd-hive/bala41288/9hTRE5gg-image.png)
](https://netbox.global/r/balanbx)

<a href="https://www.publish0x.com?a=YRdG6knybD"><img src="https://cdn.publish0x.com/prod/fs/images/da2424398bd4625ed05e95650fd1fc59638d98e68350cf673d8b393558969644.png" width="728" height="90" /></a>

</center>

---

Posted Using [LeoFinance <sup>Beta</sup>](https://leofinance.io/@bala41288/sql-script-to-get-2020-data-from-hive-blockchain)