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.
 |
 |
 |
 |
 |
 |
Hey, this was cool!
I would like to ask you, the data in December 2021 - are not complete, right? Because we are still waiting for some posts to pay out.
I was thinking of a way for someone to fetch all their posts of the past, for a specific hashtag, would that possible? i.e. If I wish to see all my posts with the tag hivestockphotos (because I did not keep them)
I may have some more ideas, I ll be in touch!
Thanks again and happy new year!
Posted Using LeoFinance Beta
Yeah there was a small bug in calculating the December 31st data. I have fixed it. It should hopefully be good now.
Yeah, I will try to add a feature to search based on tag soon. it is a big tricky I guess but let me give it a try. Thanks for the suggestion.
Very helpful! thanks!
You are welcome. Cheers!
Wow this is a nice tool. I like that I can see the historical data in the past for my account as well.
Posted Using LeoFinance Beta
Glad you liked it. Cheers!
This post has been manually curated by @bhattg from Indiaunited community. Join us on our Discord Server.
Do you know that you can earn a passive income by delegating to @indiaunited. We share 100 % of the curation rewards with the delegators.
Here are some handy links for delegations: 100HP, 250HP, 500HP, 1000HP.
Read our latest announcement post to get more information.
Please contribute to the community by upvoting this comment and posts made by @indiaunited.
This post has been manually curated by @bhattg from Indiaunited community. Join us on our Discord Server.
Do you know that you can earn a passive income by delegating your Leo power to @india-leo account? We share 100 % of the curation rewards with the delegators.
Please contribute to the community by upvoting this comment and posts made by @indiaunited.
Congratulations @balaz! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s):
Your next target is to reach 200 upvotes.
You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOPCheck out the last post from @hivebuzz:
Support the HiveBuzz project. Vote for our proposal!
It's not working for me.... I think k you need to add enter or go option. ...anyhow mere putting the name is not pulling any data
After putting the name, you should click on the buttons
Do you think I should change this user experience? Two people already said the same thing. 🤔🤔
Yes changing a but if interface would be great, actually the button looks like the tab, might be change the color or button or include an extra button called Get Data.
Cool. I will see what I can do. 👍
You have to update the year too, 2022 is not showing