select payer,

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

from flow.core.fact_transactions

where block_timestamp between '2022-06-01' and '2022-06-07'

group by 1

order by 2

),

month_later_wallets as (

select payer,

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

from first_week_wallets

join flow.core.fact_transactions using(payer)

where block_timestamp between '2022-07-01' and '2022-07-07'

group by 1

order by 2

),

time_dif as (

select payer,

first_week_tx_date,

month_later_tx_date,

timestampdiff(sql_tsi_day,first_week_tx_date,month_later_tx_date) as "Time between transactions"

from first_week_wallets

join month_later_wallets using(payer)

)

select count(case when "Time between transactions" < 30 then 1 end) as "Active accounts"

from time_dif