like_post_list as (

select

tx_hash,

block_timestamp,

signer_id,

split(try_parse_json(node_data:like):key:path, '/') [0] as account

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 block_timestamp::date<current_date

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 try_parse_json(node_data:like):value:

type = 'like'

)

account as "Account id",

count(distinct tx_hash) as "Likes",

row_number() over (order by "Likes" desc ) as "rank"

like_post_list

group by

"Account id"

order by

"Likes" desc