from near.social.fact_decoded_actions)

,

final_2 as ( select lag(date, 1) ignore nulls over (partition by signer_id order by date asc) as tx_date,

datediff('day',tx_date, date) as n_days, signer_id

from final

qualify tx_date is not null

select count(DISTINCT(signer_id)) as total_user,

case when n_days = 0 then 'Same Day'

when n_days = 1 then '1 Day'

when n_days between 1 and 7 then 'Less than a week'

when n_days between 8 and 30 then 'Less than a month'

when n_days between 31 and 60 then 'After a month'

when n_days between 61 and 90 then 'After Two months'

when n_days between 91 and 120 then 'After Three months'

when n_days between 121 and 150 then 'After Four months'

when n_days between 151 and 180 then 'After Five months'

when n_days between 181 and 210 then 'After Six months'

when n_days > 210 then 'More than six months' end as cat

from final_2

group by 2

having cat is not null