Using HiveSQL to query some Hive Data - Top Rebloggers & Top Author Reblogged

in #hive10 months ago (edited)

hivegradient.png

Hi guys!

I'm far from a database expert but still remember SQL from long time ago when there were no objects or libraries and we had to develop our own DLL, in the good old days of Borland C++, Informix or SCO UNIX.

Hidden Spoiler Text

Yes I started code before Linux! lol

Does anyone remember those funny times?
1588511876842.png

Well, let's stop being nostalgic and lets SQL lol

I've used HiveSQL https://docs.hivesql.io/ to this very simple query!

Time frame is first 4 months of 2023 and the query is, actually 3 queries because it make sense.

  • Top 20 authors reblogged
  • Top 20 Rebloggers
  • Top 10 authors reblogged by the top reblogger
    1588511876842.png

Top 20 authors reblogged (Jan to Apr 2023)

SQL Query


SELECT TOP 20  COUNT(account), author
FROM Reblogs  (NOLOCK)
WHERE CONVERT (DATE, timestamp) BETWEEN '2023-01-01' AND '2023-04-30'
GROUP BY author
ORDER BY COUNT(account) DESC

Number of ReblogsAuthor
6584ebargains
3709noctury
2631taskmaster4450
2512ocd
2138galenkp
2060arcange
1805yeckingo1
1795readthisplease
1779splinterlands
1738curangel
1676mariajcastro28
1627acidyo
1620ifarmgirl
1429heartbeatonhive
1369joetunex
1357tattoodjay
1340alive.chat
1320ladiesofhive
1309monica-ene
1308nkemakonam89

Top 20 Rebloggers (Jan to Apr 2023)

SQL Query


SELECT TOP 20  COUNT(author), account
FROM Reblogs  (NOLOCK)
WHERE CONVERT (DATE, timestamp) BETWEEN '2023-01-01' AND '2023-04-30'
GROUP BY account
ORDER BY COUNT(author) DESC

RebloggerNumber of posts reblogged
marcocasario23377
gasaeightyfive22066
cribbio22057
oadissin18403
dgi16195
pixresteemer7500
psyber-x5469
flaxz.alive4526
hive.games4401
omarrojas4239
valenpba3100
sacra972765
roronoa462396
dynamicgreen2389
jongcl2295
psyberkiller2268
flaxz.ctp2253
edgerik2245
alizmarpaola2226
mariajcastro282202

Top 10 authors reblogged by the top reblogger (Jan to Apr 2023)

SQL Query


SELECT TOP 10 COUNT(author), author
FROM Reblogs  (NOLOCK)
WHERE CONVERT (DATE, timestamp) BETWEEN '2023-01-01' AND '2023-04-30' AND account ='marcocasario'
GROUP BY author
ORDER BY COUNT(author) DESC

AuthorNumber of posts reblogged
taskmaster4450267
readthisplease264
arcange216
hironakamura197
tarazkp160
qurator156
noctury147
svanbo128
sumotori127
yolimarag126
Hope you found these simple collection of Blockchain Data interesting.

1588511876842.png

Thank for following me!

JOIN THE REVOLUTION!

Sort:  

https://leofinance.io/threads/@doze/re-leothreads-2zpdytppn
The rewards earned on this comment will go directly to the people ( doze ) sharing the post on LeoThreads,LikeTu,dBuzz.

Hey @doze. I'm so glad you shared this. I'm building some things to enhance my project and this is good for me. I'll save your post for when I need it later. Thank you!

You're most welcome