HOW TO - Daily most-followed accounts - TUTORIAL

I've been posting for the past week an update on most followed accounts per day. I see that it has caught a lot of attention. Instead of sharing the updates everyday from now on, I will share with you a fast way to retrieve this data through @hivesql.

The query is fairly simple:

SELECT TOP 10 following, 
       COUNT(*) as total
FROM (
    SELECT follower, 
           following, 
           YEAR([timestamp]) as year_time,
           MONTH([timestamp]) as month_time, 
           DAY([timestamp]) as day_time 
FROM (
    SELECT [ID],
           JSON_VALUE([json], '$[1].follower') as follower,
           JSON_VALUE([json], '$[1].following') as following,
           JSON_VALUE([json], '$[1].what[0]') as what,
          [timestamp]
FROM [DBHive].[dbo].[TxCustoms] where tid like 'follow') t
where follower is not null and following is not null and what is not null and what like 'blog') p
where year_time like 2021 and month_time like 11 and day_time 15
GROUP by following
order by total desc;

This will generate the following result:

account	total followers
@lovesniper	15
@ecency	14
@splinterlands	13
@punksonhive	0
@blockheadgames	8
@hivebuzz	8
@ocdb	7
@roelandp	7
@stever82	7
@theycallmedan	7

Currently working on setting up my own HiveStats platform in python, mainly for personal use and tracking some of the most interesting developments of this platform. Stay tuned if you want to know more about it!