select block_timestamp, FROM_ADDRESS as user_addreess, row_number() OVER (partition by user_addreess order by block_timestamp) as rn

from ethereum.core.fact_transactions

where STATUS = 'SUCCESS'

and block_timestamp >= '2022-05-30'

), first_tx as (

select block_timestamp as first_date, user_addreess

from txs

where rn = 1

), second_tx as (

select block_timestamp as second_date, user_addreess

from txs

where rn = 2

), diff as (

select *, DATEDIFF(hour, first_date, second_date) as duration

from first_tx left join second_tx using(user_addreess)

select

case when duration <= 1 then '1. Under 1 hour'

when duration between 1 and 24 then '2. Under 1 day'

when duration between 24 and 48 then '3. Under 2 days'

when duration between 48 and 168 then '4. Under 1 week'

when duration between 168 and 720 then '5. Under 1 month'

else '6. After 1 month'

end as label,

count(distinct user_addreess) as users_count

from diff

group by 1

order by 1