MLDZMNtopic3
    Updated 2023-11-19
    -- forked from topic1 @ https://flipsidecrypto.xyz/edit/queries/49b20497-e541-42bd-bf1f-4175d3c09d52

    --Thanks to rmas for decoding like actions

    with t1 as ( SELECT a.tx_hash
    , a.block_timestamp
    , (CASE WHEN replace(split(a.node_data[k.value]::string, '":')[1], '}', '')::string = 'null'
    THEN 'UNFOLLOW' ELSE 'FOLLOW' END) AS action
    , a.signer_id AS follower_account_id
    , f.value::string AS followed_account_id

    FROM near.social.fact_decoded_actions AS a
    , LATERAL FLATTEN (input => (CASE WHEN is_object(a.node_data) THEN object_keys(a.node_data) ELSE NULL END)) AS k
    , LATERAL FLATTEN (input => object_keys(try_parse_json(a.node_data[k.value]))) AS f

    WHERE a.node = 'graph'
    AND k.value = 'follow'
    and action='FOLLOW'
    )

    select
    followed_account_id as "Followed user",
    count(distinct follower_account_id) as "Followers",
    count(distinct tx_hash) as "Follows"
    from t1
    group by 1
    order by 2 desc limit 10


    Run a query to Download Data