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

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 Hive account@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.

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.