Hive NEW USERs - TREND - Acitivity & Hive POWERUps Analysis - Bonus HivePowerUp trends for Users Reputation score Greater than 40...

3 comments

gungunkrishu3 KyesterdayPeakD6 min read

Hey All,

Following up on my last post which was around Hive PowerUp trends -

. I got an intresting comment from @behiver who wanted to know about the details of #HIVE users who are cashing out frequently and are not invested in harsh words we can say are milking the system. These users are selling periodically without putting to much consideration in the Hive tokenomics anymore. Inorder to know about these type of users we already have KE ratio score [rewards/stake] and we can get some idea about these users activity. But from whole of this, I got an idea to explore new users activity on the #HIVE blockchain and see how their Hive Power evolves. Sounds like an intresting idea? Instead directly jumping into a complex query, I started off to find out top50 new users who have powered up #HIVE..

Top 50 new users who powered UP HIVE

The condition here I took to eliminate accounts which are just created and is likely to have any activity checking their reputation score being > greater than 30. The above image has the query and the output of it and hence its a working query that tell us that there are users with rep_score > 30 that do #hive power up. Ok now I can further build my query to something like look for more data i.e. new users who joined in the last 12 months and had real on-chain activity. Here is the working query and its output.

Month wiwse breakdown of new users who joined Hive in the last 1 year

SELECT 
    YEAR(n.created) AS [Year],
    MONTH(n.created) AS [Month],
    COUNT(DISTINCT n.name) AS [ActiveNewUsers]
FROM Accounts n
WHERE n.created >= DATEADD(YEAR, -1, GETDATE())
  AND n.reputation > 30
  AND (
        EXISTS (SELECT 1 
                FROM Comments c 
                WHERE c.author = n.name 
                  AND c.created >= DATEADD(YEAR, -1, GETDATE()))
     OR EXISTS (SELECT 1 
                FROM VOTransferToVestingCompleteds v 
                WHERE v.to_account = n.name 
                  AND v.[timestamp] >= DATEADD(YEAR, -1, GETDATE()))
      )
GROUP BY YEAR(n.created), MONTH(n.created)
ORDER BY [Year], [Month];

Output - New Users Joined in last 1 YEAR and has rep_score > 30

Year	Month	Active New Users
2024	8	188
2024	9	579
2024	10	716
2024	11	676
2024	12	1926
2025	1	1452
2025	2	936
2025	3	715
2025	4	784
2025	5	953
2025	6	847
2025	7	851
2025	8	556

Now what? Well this was not it. I wanted to do something further and get to a point where I have somwething tied to acitiviteis posting/commenting/powerup i.e.

Bloggers - wrote at least one blog post
Commenters - wrote at least one comment
PowerUppers - performed Hive powered up

Here is what I got from running the query. Please bear in mind I am filtering based on rep_score > 30. Instead of making it simple to understand, I leverage my Python skills here to plot the following different visuals. Have a look at them and see if you are able to find something intresting in it.

📈 Line chart: total new users each month

📊 Stacked bar: Bloggers vs Commenters vs Hive PowerUps

🟪 Line chart: users who did all three activities

📉 Percentage breakdown (Users Engagement)

After having all this data plotted for 1 Year user activity. I started to wonder around - what would it look like if I further drill down to 5 Years of data and do some modification where the rep_score to look for should be > greater than 40 and look at their #HIVE powerup trends. Intresting it will be RIGHT? Ok without a further ado here is the snaphot of my DBeaver environment with running query and its output; where I seggrated users based on different #Hive Powerup categories they are in ie. 100 to 200, 200 to 300, 300 to 500 and 500 above.

Intrested in seeing the graphs plotted out of this data? Then here it is.

Overall close to 30% of new users land up having #HIVE greater than 500+ not sure if this healthy or not. For the past two years the number is less than 100 which I feel is not that great. Ok I guess this should be it for todays post on - "Hive NEW USERs - TREND - Acitivity & Hive POWERUps Analysis - Bonus HivePowerUp trends for Users Reputation score Greater than 50..." Let me know what you guys were able to spot on these trend analysis for the new users activity on the Hive Blockchain. I hope I was able to explain things clearly. If you have any follow-up questions or doubts, please feel free to share them in the comments below. Happy Learning with HiveSQL....Cheers...

Hive NEW USERs - TREND - Acitivity & Hive POWERUps Analysis - Bonus HivePowerUp trends for Users Reputation score Greater than 40...

#Hive #Blockchain #Crypto #HiveSQL #PowerUpTrend #HiveCommunity #Web3 #Decentralization #HiveBlog

Best Regards
Paras

Image Courtesy:: pro canva license, hiveblocks, DBeaver

PS:- None of the above is a FINANCIAL Advice. Please DYOR; Do your own research. I've have an interest in BlockChain & Cryptos and have been investing in many emerging projects.