select row_number() over (partition by payer order by block_timestamp asc) as rank, payer, block_timestamp

from flow.core.fact_transactions

where block_timestamp::date >= '2022-05-15'

),

second_tx as(

select payer, block_timestamp as date

from top

where rank = 2

),

first_tx as(

select payer, block_timestamp as date

from top

where rank = 1

),

only1tx as (

select (sum(iff(rank = 1, 1, 0)) - sum(iff(rank = 2, 1, 0))) as only_one_tx

from top

select case

when datediff(day, f.date, s.date) <= 7 then 'less than a week'

when datediff(day, f.date, s.date) > 7 and datediff(day, f.date, s.date) <= 30 then 'between a week and a month'

when datediff(day, f.date, s.date) > 30 and datediff(day, f.date, s.date) <= 180 then 'between a month and 6 month'

else 'more than 6 month' end as type, count(*) as "count"

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

group by 1

union all

select 'only one tx' as type, only_one_tx

from only1tx