select BUYER as user,min(block_timestamp::DATE) as date1

from flow.core.ez_nft_sales where BLOCK_TIMESTAMP >='2022-01-01'

group by 1

),

second_tx as (select BUYER as user1,min(block_timestamp::DATE) as date2 from flow.core.ez_nft_sales a join first_tx b where BUYER=b.user and block_timestamp::date > date1 and BLOCK_TIMESTAMP >='2022-01-01'

group by 1),

date_diff as (

select user1,datediff ('day',date1,date2) as time_dif from first_tx a join second_tx b on a.user=b.user1

SELECT CASE

WHEN time_dif <= 1 THEN 'Transaction in Same Day'

WHEN time_dif > 1 AND time_dif <= 2 THEN 'Transaction between 1-2 Days'

WHEN time_dif > 2 AND time_dif <= 5 THEN 'Transaction between 2-5 Days'

WHEN time_dif > 5 AND time_dif <= 7 THEN 'Transaction between 5-7 Days'

WHEN time_dif > 7 AND time_dif <= 14 THEN 'Transaction between 7-14 Days'

WHEN time_dif > 14 AND time_dif <= 21 THEN 'Transaction between 14-21 Days'

WHEN time_dif > 21 AND time_dif <= 30 THEN 'Transaction between 21-30 Days'

WHEN time_dif > 30 AND time_dif <= 90 THEN 'Transaction between 30-90 Days'

WHEN time_dif > 90 AND time_dif <= 180 THEN 'Transaction between 90-180 Days'

ELSE 'Transaction after 180 Days' end as retention,

count(distinct user1) as users

from date_diff

group by 1

order by users desc