select

tx_hash,

block_timestamp,

signer_id,

try_parse_json(node_data:like):value:type as type

from near.social.fact_decoded_actions

join near.core.fact_transactions using(tx_hash)

where tx_status = 'Success'

and node_data:like is not null

and node = 'index'

and split(try_parse_json(node_data:like):key:path,'/')[2] = 'main'

and split(try_parse_json(node_data:like):key:path,'/')[2] is not null

and block_timestamp::date<current_date

select

case when type = 'unlike' then 'Unlike' else 'Like' end as "Type",

count(distinct tx_hash) as "Like action",

count(distinct signer_id) as "Accounts"

from a

group by "Type"

order by "Type" asc