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)

, txs as (select

tx_signer ,

min(block_timestamp) as fs_tx,

max(block_timestamp) as last_tx

from

near.core.fact_transactions

where

tx_signer in (select signer_id from base where first_day >= CURRENT_DATE - 90)

and BLOCK_TIMESTAMP > CURRENT_DATE - 180

group by 1)

, fin as (select

TX_SIGNER,

datediff(day,fs_tx,last_tx) as age_days

from txs

select

case

when age_days < 1 then 'less than 1 day'

when age_days between 1 and 7 then 'less than 1 week'

when age_days between 7 and 30 then 'less than 1 month'