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

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