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