MLDZMNXAR11
    Updated 2023-01-27
    with tb1 as (select
    distinct purchaser as u1,
    min(BLOCK_TIMESTAMP) as x1
    from solana.core.fact_nft_sales
    where SUCCEEDED='TRUE'
    and MARKETPLACE ='exchange art'
    and SALES_AMOUNT>0
    group by 1 ),

    tb2 as (SELECT
    distinct SELLER as u2,
    min(BLOCK_TIMESTAMP) as x2
    from solana.core.fact_nft_sales
    where SUCCEEDED='TRUE'
    and MARKETPLACE ='exchange art'
    and SALES_AMOUNT>0
    and SELLER in (select u1 from tb1)
    group by 1) ,

    tb3 as (select
    tb1.u1 as purchasers,
    avg(DATEDIFF(day,x1, x2 )) as time_between
    from tb1
    join tb2 on tb1.u1=tb2.u2
    group by 1)
    select
    case when time_between<1 then 'whitin 24 Hours'
    when time_between>=1 and time_between<7 then 'Under 1 week'
    when time_between>=7 and time_between<30 then 'Between 1 week to 1 month'
    when time_between>=30 then 'Over 1 month'
    end as buckets,
    count(distinct purchasers) as count_users
    from tb3
    group by 1
    Run a query to Download Data