with

base as

( SELECT

signer_id,

min(date_trunc('day', block_timestamp)) as first_day,

count(DISTINCT tx_hash) as profile_change_events

FROM near.social.fact_addkey_events

GROUP BY 1)

select

date_trunc(month, block_timestamp)::date as month,

label_type ,

count(distinct tx_hash) txns,

row_number()over(partition by month order by txns desc) as rank

from

near.core.fact_transactions , near.core.dim_address_labels

where

TX_SIGNER in (select signer_id from base )

and

tx_receiver = address

and BLOCK_TIMESTAMP > CURRENT_DATE - 180

group by 1 ,2

qualify rank <= 3