with add_post_list as (

select

tx_hash,

block_timestamp,

signer_id,

node,

parse_json(node_data:main) as json_main

from near.social.fact_decoded_actions

join near.core.fact_transactions using(tx_hash)

where tx_status = 'Success' and block_timestamp::date<current_date

and node = 'post'

and json_main is not null

select

signer_id as "Account id",

count(distinct tx_hash) as "Number of Posts",

row_number() over(order by "Number of Posts" desc) as "rank"

from add_post_list

group by "Account id"

order by "Number of Posts" desc

limit 8