Hey All,
The journey to explore and learn while querying HiveSQL which is a publicly available Microsoft SQL database containing all the Hive blockchain data continues. This time I wanted to do something different and explore/find users who are close to my reputation score. I will be further building this query. For now, this post is to demonstrate T-SQL style queries, since HiveSQL runs on MS SQL Server. We will also be seeing an example as to how to convert the raw_reupation score here on HIVE to a human redable form. And just that you all may know - Hive reputation score is built using a logarithmic formula that converts raw blockchain reputation into a readable 25–80+ scale. So lets first get this understanding correct and then move on to querying the HiveSQL database server.
Here is the correct formula for convering the raw reputation number to a redeable reputation #HIVE user score.
Hive_rep_score = (log10(max(rep_raw, 1)) - 9) * sign(rep_raw) * 9 + 25
Lets compute my raw_reputation score which is "1,073,400,758,379,282" as seen on hiveblocks.com/@gungunkrishu
- Raw (as integer)- raw = 1,073,400,758,379,282
- Absolute value: |raw| = 1,073,400,758,379,282
- log10(|raw|): log10(1073400758379282) = 15.030761897788935
- Subtract 9: 15.030761897788935 - 9 = 6.030761897788935
- Multiply by 9: 6.030761897788935 * 9 = 54.27685708010041
- Add 25: 54.27685708010041 + 25 = 79.27685708010041
Final Readable Reputation Score:** - Rounded to 1 decimal -- 79.3 [my redeable reputation score]
Hive Reputation Explained: Step-by-Step Conversion Guide - with an EXAMPLE
Moving forward, have a look at the above image captured from DBeaver. My query started running without any errors and produced the correct output, which I manually verified against HiveBlocks. Here is the running query that gets me top10 users whoes reputation is close to mine.
WITH base AS (
SELECT
name,
reputation,
CASE
WHEN reputation = 0 THEN 25
ELSE
(LOG10(CAST(ABS(reputation) AS FLOAT)) - 9)
* (CASE WHEN reputation > 0 THEN 1 ELSE -1 END) * 9 + 25
END AS rep_score
FROM Accounts
),
closest AS (
SELECT TOP 11 *
FROM base
ORDER BY ABS(rep_score - (SELECT rep_score FROM base WHERE name = 'gungunkrishu'))
)
SELECT *
FROM closest
ORDER BY rep_score DESC;
Output of Query - Top10 Users close to my reputation @gungunkrishu
| HIVE Username | Raw Reputation | Rep Score |
|---|---|---|
| susanli3769 | 1096361119096365 | 79.36 |
| cryptopassion | 1095186992075950 | 79.36 |
| omarcitorojas | 1092011866627470 | 79.34 |
| steemflow | 1091781243463811 | 79.34 |
| ssekulji | 1089522889595497 | 79.34 |
| stayoutoftherz | 1082957703152084 | 79.31 |
| gungunkrishu | 1073538370191469 | 79.28 |
| teamukraine | 1064252086102883 | 79.24 |
| bengy | 1063970651275913 | 79.24 |
| creativecrypto | 1055131252444029 | 79.21 |
| josehany | 1050318330940611 | 79.19 |
Overall it was a great learning expereince for me to pull records and get to know hive users who are having a reputation score as close to me. As stated above next would be seeing the Hive Power and Reputation Score analysis. It is always good to see how these two metrics work together in reflecting an account’s influence and growth on Hive. Ok I guess this should be it for todays post on - "Hive Reputation Explained: Step-by-Step Conversion Guide - with an EXAMPLE..." I hope I was able to explain it clearly. If there are any follow up questions/doubts - Please feel free to reach out on the comment box below. Happy Learning - HiveSQL....cheers
Hive Reputation Explained: Step-by-Step Conversion Guide - with an EXAMPLE...
#Hive #Blockchain #Crypto #HiveSQL #Reputation #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.