Ever since I started using Hive SQL, I always wanted to create a portal from where we can easily view all the possible data we can fetch from Hive with the help of Hive SQL. I would like to start this article by conveying my thanks and regards to @arcange. I don't think this tool would have been a possibility without his Hive SQL. I also have plans to continue working on this tool and even make this an open-source project in the future. The website/tool is accessible from the below location. https://hivedata.live The website is live but there is a high possibility for bugs and errors. Probably you can consider this as an alpha version. If you find any bugs or any improvement suggestions, please reach out to me on Discord or in the comments section. ![image.png](https://images.hive.blog/DQmUwJDxPAW4GkNBskLycD6RToocwHkZc1oR51g1p8sfDRj/image.png) The user interface of the website is pretty simple right now. It will be improved in the future as the application is progressing. The website now offers two different types of data right now. Some of my friends asked me about an option to view their year-end data. I thought it would be better to give them a tool instead of giving them data individually. As the year is just starting, I wanted to release this as soon as possible for the users to view their year-end data. The tool displays the Author Rewards, Curation Rewards, Total Posts, and Total Comments from the year we choose for the selected account. The data will be grouped month-wise and displayed for the user. Please use the website and let me know your observations in the comments section. I would like to share the SQL queries used to view each of these options one by one. If someone is willing to directly query Hive SQL, they can make use of the following queries. ## Author Rewards Those who can read the below query should be familiar with where to change the account name to get the result for your account details. ``` DECLARE @account AS VARCHAR(16) = 'bala41288' DECLARE @year AS INT = 2021 DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year)) -- start of year DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year DECLARE @HPV DECIMAL(19,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties); SELECT @year AS [year], MONTH(timestamp) AS [month], SUM(hbd_payout) AS totalHBDPayout, SUM(hive_payout) AS totalHivePayout, SUM(vesting_payout) AS totalVestingPayout, CAST(SUM(vesting_payout) * @HPV AS DECIMAL(18,3)) AS totalHP FROM VOAuthorRewards WHERE author = @account AND (timestamp >= @SOY1 AND timestamp < @SOY2) GROUP BY MONTH(timestamp) ORDER BY MONTH(timestamp) ``` ## Curation Rewards ``` DECLARE @account AS VARCHAR(16) = 'bala41288' DECLARE @year AS INT = 2021 DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year)) -- start of year DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year DECLARE @HPV DECIMAL(19,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties); SELECT @year AS [year], MONTH(timestamp) AS [month], SUM(reward) AS totalRewardInVests, CAST(SUM(reward) * @HPV AS DECIMAL(18,3)) AS totalHP FROM VOCurationRewards WHERE curator = @account AND (timestamp >= @SOY1 AND timestamp < @SOY2) GROUP BY MONTH(timestamp) ORDER BY MONTH(timestamp) ``` ## Total Posts ``` DECLARE @account AS VARCHAR(16) = 'bala41288' DECLARE @year AS INT = 2021 DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year)) -- start of year DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year SELECT @year AS [year], MONTH(created) AS [month], count(permlink) AS totalPosts FROM "DBHive"."dbo"."Comments" WHERE author = @account AND created >= @SOY1 AND created < @SOY2 AND parent_author = '' GROUP BY MONTH(created) ORDER BY MONTH(created) ASC ``` ## Total Comments ``` DECLARE @account AS VARCHAR(16) = 'bala41288' DECLARE @year AS INT = 2021 DECLARE @SOY1 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year)) -- start of year DECLARE @SOY2 AS DATE = CONVERT(DATE,FORMATMESSAGE('%i-01-01', @year+1)) -- start of next year SELECT @year AS [year], MONTH(created) AS [month], count(permlink) AS totalComments FROM "DBHive"."dbo"."Comments" WHERE author = @account AND created >= @SOY1 AND created < @SOY2 AND parent_author <> '' GROUP BY MONTH(created) ORDER BY MONTH(created) ASC ``` I was thinking that the above will be the most common requirement to view the stats from the previous year. If you think I should be adding some more features to the site or some more queries, please let me know in the comments section. I can work on the same. ---

---

Introducing hivedata.live v0.1.0 - A tool to visualize Hive Data with the help of Hive SQL