HiveSQL - Top 50 #HIVE Reputable Users - HIVE POWER & #HBD in Savings they #HODL...

[gungunkrishu]

Hey All,

Diving deep into HiveSQL and getting my hands dirty one more time. This time, I wanted to retrieve records related to Hive users, their Reputation, Hive Power, and the HBD they are holding. After some effort, I finally managed to fetch the data. Here’s a step-by-step summary of how I created the queries, along with their outputs.

---

First query triggered to get top 50 users and their Reputation i.e. highest reputation accounts appear first.

SELECT TOP 50 
    name, 
    reputation_ui
FROM Accounts
ORDER BY reputation_ui DESC;

First Query Output

Username	Reputation
hbd.funder	99.81
buildawhale	89.30
taskmaster4450	85.57
haejin	85.53
oflyhigh	85.21
tarazkp	85.12
ocd	84.74
chbartist	83.60
poshtoken	83.35
galenkp	83.06
steemcleaners	83.01
edicted	82.92
acidyo	82.89
gooddream	82.88
burnpost	82.82
cryptoandcoffee	82.70
deanliu	82.68
jrcornel	81.96
kingscrown	81.91
khaleelkazi	81.85
josediccus	81.75
zaku	81.72
alokkumar121	81.69
slobberchops	81.53
tattoodjay	81.46
papa-pepper	81.44
erikah	81.41
traf	81.35
joythewanderer	81.32
daveks	81.25
papilloncharity	81.25
trumpman	81.24
revisesociology	81.18
kkarenmp	81.07
taskmaster4450le	81.04
themarkymark	81.02
nainaztengra	80.99
adsactly	80.88
bozz	80.82
jongolson	80.78
meesterboom	80.76
derangedvisions	80.75
riverflows	80.73
daltono	80.72
pinmapple	80.62
travelgirl	80.57
acesontop	80.50
anggreklestari	80.50
splinterlands	80.47
priyanarc	80.45

---

Second query triggered was to add the Hive Power to their names and for that I used the DynamicGlobalProperties table that contains the data returned by the get_dynamic_global_properties API. This API call is used to get the state of the Hive blockchain and its global parameters. It is updated every time a block is processed. As I wanted to know the HIVE POWER status of these accounts, I had to use this table DynamicGlobalProperties

WITH Globalinfo AS (  -- creating a temporary table
    SELECT 
        CAST(total_vesting_fund_hive AS FLOAT) / CAST(total_vesting_shares AS FLOAT) AS Hive_Per_VEST
    FROM DynamicGlobalProperties
)
SELECT TOP 50 
    A.name, 
    A.reputation_ui, 
    A.vesting_shares * G.Hive_Per_VEST AS Hive_Power
FROM Accounts A
CROSS JOIN Globalinfo G  -- attaching the Hive Power conversion factor to each user
ORDER BY A.reputation_ui DESC;  -- ensuring the highest reputation users appear first

Second Query Output

Username	Reputation	Hive Power
hbd.funder	99.81	22.80904503143511
buildawhale	89.30	63933.480950594254
taskmaster4450	85.57	294402.85523154336
haejin	85.53	7506.131346795173
oflyhigh	85.21	158739.17842221
tarazkp	85.12	206391.52021431507
ocd	84.74	14108.930751724036
chbartist	83.60	6.2213732482631485
poshtoken	83.35	17103.58297225259
galenkp	83.06	253648.50816326542
steemcleaners	83.01	31201.186648666004
edicted	82.92	225380.76027778126
acidyo	82.89	153252.6913070298
gooddream	82.88	68543.73626491279
burnpost	82.82	0.0
cryptoandcoffee	82.70	147921.37715045665
deanliu	82.68	122787.91947811435
jrcornel	81.96	9.284129986643281
kingscrown	81.91	5.090095234385113
khaleelkazi	81.85	9942.439921362778
josediccus	81.75	83161.26949810807
zaku	81.72	1891.0733531690853
alokkumar121	81.69	41330.90397081008
slobberchops	81.53	236818.2228582315
tattoodjay	81.46	130048.08224456086
papa-pepper	81.44	1034.9461719312715
erikah	81.41	102248.02457043192
traf	81.35	151676.5954351633
joythewanderer	81.32	0.0004950950586006
daveks	81.25	270371.70382043754
papilloncharity	81.25	131913.8686101904
trumpman	81.24	72915.84073488567
revisesociology	81.18	130391.99343459756
kkarenmp	81.07	6492.898659816029
taskmaster4450le	81.04	121943.09867273988
themarkymark	81.02	1399733.1471457137
nainaztengra	80.99	48789.639133172444
adsactly	80.88	0.5121181186786122
bozz	80.82	90060.93681083719
jongolson	80.78	20071.867594570977
meesterboom	80.76	128991.05941356548
derangedvisions	80.75	1239.6827234643556
riverflows	80.73	24699.59793434295
daltono	80.72	16685.11913732083
pinmapple	80.62	356.41296243656444
travelgirl	80.57	1155.5647758071532
acesontop	80.50	1385.6173406565365
anggreklestari	80.50	2368.236343037443
splinterlands	80.47	2550.242108615956
priyanarc	80.45	8785.971983859732

---

Now I wanted to check if these accounts are holding any HBD [Hive Backed Dollar] in their savings account or not. Here is the final query that was triggerd to get me the results of - "Top 50 #HIVE Reputable Users - HIVE POWER & #HBD in Savings they #HODL... "

Final Query - Top 50 users, Reputation, Hive POWER and HBD in Savings

WITH Globalinfo AS (
    SELECT 
        CAST(total_vesting_fund_hive AS FLOAT) / CAST(total_vesting_shares AS FLOAT) AS Hive_Per_VEST
    FROM DynamicGlobalProperties
)
SELECT TOP 50 
    A.name, 
    A.reputation_ui, 
    A.vesting_shares * G.Hive_Per_VEST AS Hive_Power, 
    A.savings_hbd_balance AS Savings_HBD -- retrieving the HBD in savings
FROM Accounts A
CROSS JOIN Globalinfo G
ORDER BY A.reputation_ui DESC;

Final Query Output

Username	Reputation	Hive Power	HBD in Savings
hbd.funder	99.81	22.809064387881783	0.000
buildawhale	89.30	63933.53520648322	0.000
taskmaster4450	85.57	294403.105070726	0.000
haejin	85.53	7506.137716725652	0.000
oflyhigh	85.21	158743.3268938631	3897.787
tarazkp	85.12	206391.6953644123	0.000
ocd	84.74	14108.942724990138	0.985
chbartist	83.60	6.22137852790898	0.000
poshtoken	83.35	17103.597486871295	0.000
galenkp	83.06	253648.7234170744	1049.309
steemcleaners	83.01	31201.213126938317	0.000
edicted	82.92	225380.9515427229	0.000
acidyo	82.89	153252.82136190683	0.000
gooddream	82.88	68543.7944332041	7957.051
burnpost	82.82	0.0	0.000
cryptoandcoffee	82.70	147921.502681019	5.416
deanliu	82.68	122788.02367966299	16087.267
jrcornel	81.96	9.284137865437275	0.000
kingscrown	81.91	5.090099553994146	0.000
khaleelkazi	81.85	9942.448358818527	1.437
josediccus	81.75	83165.19820933556	180.050
zaku	81.72	1891.0749579912278	0.009
alokkumar121	81.69	41330.93904546742	0.002
slobberchops	81.53	236818.42382935042	284.744
tattoodjay	81.46	130048.1926073036	1661.233
papa-pepper	81.44	1034.9470502179493	0.000
erikah	81.41	102248.11134120345	0.000
traf	81.35	151676.72415251762	0.000
joythewanderer	81.32	0.0004950954787533	0.000
daveks	81.25	270371.93326605705	1789.208
papilloncharity	81.25	131913.98055629598	4274.468
trumpman	81.24	72915.9026134773	2729.396
revisesociology	81.18	130392.10408919376	6074.975
kkarenmp	81.07	6492.904169886551	32.909
taskmaster4450le	81.04	121943.202157348	14236.641
themarkymark	81.02	1399737.5250034442	0.000
nainaztengra	80.99	48789.68053753842	0.000
adsactly	80.88	0.5121185532775636	0.026
bozz	80.82	90061.0132392768	8250.711
jongolson	80.78	20071.884628165946	0.000
meesterboom	80.76	128991.16887928662	3000.657
derangedvisions	80.75	1239.6837754966741	0.001
riverflows	80.73	24699.618895170097	385.792
daltono	80.72	16685.13329681845	0.032
pinmapple	80.62	356.4132648994028	0.000
travelgirl	80.57	1155.5657564544329	628.538
acesontop	80.50	1385.6185165333925	0.000
anggreklestari	80.50	2368.238352794556	0.000
splinterlands	80.47	2550.2442728286755	0.000
priyanarc	80.45	8785.979439901752	1230.740

---

So here we have the final query results; querying HiveSQL to get Top 50 users having the highest reputation their HIVE POWER and HBD in the Savings account. All the queries are tried and tested they are working absolutely fine all that you will require is to connect to the HiveSQL database and then trigger the queries its that simple.

HiveSQL - Top 50 #HIVE Reputable Users - HIVE POWER & #HBD in Savings they #HODL...

#hivesql #DBeaver #learning #hivestatistics #data #datamining #hiveaccount #liquidhive #DynamicGlobalProperties

image source:: hive.io, hive x

Best Regards
Paras

Comments

[arcange]

Using a join to compute users' HP is overkill and makes your queries less readable. Use variables:

DECLARE @HPV AS DECIMAL(18 ,8) = (SELECT hive_per_vest FROM DynamicGlobalProperties)

SELECT TOP 50 
    name, 
    reputation_ui, 
    vesting_shares * @HPV AS [Hive_Power]
FROM
    Accounts
ORDER BY 
    reputation_ui DESC

[gungunkrishu]

thank you @arcange for the query optimization. I will keep in mind moving forward. cheers

[arcange]

You're welcome @gungunkrishu. Glad to help.

[untilwelearn]

In addition to knowing the top prestigious users, this HP holding list also shows how many of these oldest users still have faith in Hive. Its Informative!

[gungunkrishu]

Absolutely correct @untiwelearn that was only the motive I had to trigger these queries and see how many users with high reputation is staking i.e. holding HP and HBD in savings account. cheers

[untilwelearn]

Bhai ek alag sawaal hai since mujhe ye coding wala tareeka nahi pata. Kya koi aisa platform bhi hai jiske through active ecency users ki sankhya pata ki ja sakti hai?

[gungunkrishu]

bhai will check if I can get this data. But most probably the hive tables wont have it and its more tied the ecency app. Ap tao khud ecency curator hao..just aap the ecency folks about the active users base of the App. cheers

[untilwelearn]

Mujhe actually GK ko ek idea suggest karna hai par usse pahle ye users ki thodi info chahiye. Ab suggestion jis research par based hai uske liye bhi GK se hi puchhunga to mere suggestion wale idea ki to waise hi koi value nahi rahegi😅 isliye koshish hai ki ye kuch info mujhe kahin aur se mil jaye.
Kuch aur users or MOD se baat karta hoon, unko to pata hi hoga. :)

[gungunkrishu]

Koi nahi mae bhi apnae side pae check karoga. In between GK kaun hae bhai. And one more idea hae ..ecency points ko token mae lao..that will become valuable. Aur bhai ecency account sae high vote ka kya criteria hae?

[untilwelearn]

😅 Bhai aap itne puraane high reputed ho yahan I thought aapko pata hoga, GK (Good Karma) is the karta-dharta(admin/owner) of Ecency.
Mujhe yahi idea to suggest karna hai inhe, waise maine kuch months pahle bhi suggest Kiya tha par tab inke alag plans lineup the abhi main kuch important statistics ke sath inko ye baat samjhana chahta hoon ki ye kitna accha decesion hoga agar EPs ko token mein convert Kiya to. Dekhte hain, hopefully is baar samajh jayenge.

Ecency se bigger vote paane ka tareeka hai ki aap ecency ke monthly curator ban jao. Tab aapko 10% ka vote mil jayega. Iske alawa voting percentage kisi curator ke hath mein nahi hoti by default 5% hi vote hota hai wo bhi agar aapne Ecency platform use karke blog banaya ho.

[indiaunited]

This post has been manually curated by @steemflow 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 more than 100 % of the curation rewards with the delegators in the form of IUC tokens. HP delegators and IUC token holders also get upto 20% additional vote weight.

Here are some handy links for delegations: 100HP, 250HP, 500HP, 1000HP.

_{100% of the rewards from this comment goes to the curator for their manual curation efforts. Please encourage the curator @steemflow by upvoting this comment and support the community by voting the posts made by @indiaunited.}.

This post received an extra 10.00% vote for delegating HP / holding IUC tokens.

[blackavalon]

This is good work. I didn't know there were so many reputable users.

[gungunkrishu]

Thank you. Glad you liked the query results. Cheers

[vixmemon]

Cool! seems like proper SQL to me...
So, is it just read-only or we can update content in SQL too and see it reflected on hive's nodes?

[gungunkrishu]

It's ready only. Cheers

[hivebuzz]

Congratulations @gungunkrishu! You have completed the following achievement on the Hive blockchain And have been rewarded with New badge(s)

You have been a buzzy bee and published a post every day of the week.

_{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 STOP}