ZSaedS2.4 Top Users following copy
    Updated 2023-03-15
    -- forked from 82a63486-4271-4702-a3cb-42405cb9a440
    with clean as (select -- remove fail tarnsaction
    a.* from near.social.fact_addkey_events a
    INNER JOIN near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.tx_status = 'Success'
    where a.BLOCK_TIMESTAMP is not null
    )
    , singin as (-- get first sing in for each user
    select min(BLOCK_TIMESTAMP) as date , SIGNER_ID as user from clean
    group by user
    )
    , period as (-- filter users base entred date
    select date , user from singin
    where date::date between '{{Start_from}}' and '{{End_At}}'
    )

    , flat as (
    with reg as (
    select a.*, parse_json(REGEXP_REPLACE(NODE_DATA,'null','"unfollow"')) as data from
    near.social.fact_decoded_actions a
    INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'
    where NODE ='graph'
    and NODE_DATA:follow is not null
    )
    select
    *
    from reg,
    table(
    FLATTEN( INPUT =>data:follow)
    )
    -- where NODE ='graph'
    -- and NODE_DATA:follow is not null
    )
    , follow as (
    select SIGNER_ID,key, max(BLOCK_TIMESTAMP) as date from flat
    where value !='unfollow'
    Run a query to Download Data