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] = 'comment'

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

select

date_trunc('day', block_timestamp) as "Date",

case

when type = 'unlike' then 'Unlike'

else 'Like'

end as "Type",

count(distinct tx_hash) as "Total Number of Comments Like"

from like_comment_list

group by "Date", "Type"