with add_comment_list as (

select

tx_hash,

block_timestamp,

signer_id,

node,

parse_json(node_data:comment) as jsona_comment,

parse_json(node_data:comment):item:path as jsona_item_path,

split(jsona_item_path, '/')[0] as account

from near.social.fact_decoded_actions

join near.core.fact_transactions using(tx_hash)

where tx_status = 'Success'

and node = 'post'

and jsona_comment is not null

and jsona_item_path is not null

select

account as "Account id",

count(distinct tx_hash) as "Comments",

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

from add_comment_list

group by "Account id"

order by "Comments" desc