--CC @Panda-gXSkiX

-- forked from panda-gXSkiX / Near Social | Retention Rates of Users @ https://flipsidecrypto.xyz/panda-gXSkiX/q/Xdf-tFDtOm1M/near-social-retention-rates-of-users

WITH raw_t AS

SELECT

'Post' as tx_type,

a.tx_hash,

a.tx_signer as user_address,

a.block_timestamp

FROM

near.core.fact_transactions a join near.social.fact_posts b on a.tx_hash = b.tx_hash

WHERE

TX_RECEIVER = 'social.near'

AND

TX_STATUS = 'Success'

UNION ALL

SELECT

CASE

when node_data:follow is not NULL then 'Follow'

when node_data:main is not NULL then 'Main'

when node_data:post is not NULL then 'Post'

when node_data:like is not NULL then 'Like'

when node_data:graph is not NULL then 'Graph'

else NODE

end as tx_type,

a.tx_hash,

a.tx_signer as user_address,

a.block_timestamp

near.core.fact_transactions a join near.social.fact_decoded_actions b on a.tx_hash = b.tx_hash

WHERE