select BLOCK_TIMESTAMP, payer, ROW_NUMBER() OVER (partition by payer order by BLOCK_TIMESTAMP) as number_of_tx from flow.core.fact_transactions

where block_timestamp::date>CURRENT_DATE-90 )

,

first_tx as (

select BLOCK_TIMESTAMP as first_transaction, payer from a

where number_of_tx=1)

,

second_tx as (

select BLOCK_TIMESTAMP as second_transaction,payer from a

where number_of_tx=2)

,

duration as (

select first_tx.payer, avg(DATEDIFF(day,first_transaction, second_transaction )) as Intervall

from first_tx join second_tx on first_tx.payer=second_tx.payer

group by 1)

select

when Intervall <= 7 then 'returned before a week'

when Intervall > 7 and Intervall < 30 then 'returned before a month'

else 'returned after a month'

end as retention,

count(distinct payer) as count_users

from duration

group by 1

limit 100