with only_post as (

select *

from near.social.fact_decoded_actions

,table(FLATTEN( INPUT =>NODE_DATA))

where NODE ='index'

and key = 'like'

)

, data as (

select a.* , a.NODE_DATA:"like" as v

, TRY_PARSE_JSON(VALUE):key:path as pat

, REGEXP_SUBSTR(pat,'(.*)\/post\/(.*)',1 , 1, 'e',1 ) as user_like

, REGEXP_SUBSTR(pat,'(.*)\/post\/(.*)',1 , 1, 'e',2 ) as user_type_like

from only_post a INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'

where TRY_PARSE_JSON(VALUE):key:type ='social'

)

, like_comment as (

select a.* from data a

INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'

where user_type_like = 'comment'

, like_post as

(select a.* from data a

INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'

where user_type_like = 'main'

select