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