with 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,

FLATTEN( INPUT =>data:follow)) )

, follow as (

select SIGNER_ID,key, max(BLOCK_TIMESTAMP) as date from flat

where value !='unfollow'

group by SIGNER_ID,key

,unfollow as

select SIGNER_ID,key, max(BLOCK_TIMESTAMP) as date from flat

where value ='unfollow'

group by SIGNER_ID,key

,abslot_unfollow as (

select unfollow.SIGNER_ID,unfollow.key

from unfollow JOIN follow on(unfollow.SIGNER_ID = follow.SIGNER_ID AND

unfollow.key = follow.key and unfollow.date > follow.date

, following_count as (

select follow.SIGNER_ID as user,count(DISTINCT follow.key) as num_following

from follow left join abslot_unfollow as b on (