MLDZMNavgtime- Solana
    Updated 2022-06-21
    with tb1 as (select
    BLOCK_TIMESTAMP,
    TX_FROM,
    ROW_NUMBER() OVER (partition by TX_FROM order by BLOCK_TIMESTAMP) as t_n
    from solana.core.fact_transfers
    ),

    tb2 as (select
    BLOCK_TIMESTAMP as first_transaction,
    TX_FROM
    from tb1
    where t_n=1),

    tb3 as (select
    BLOCK_TIMESTAMP as second_transaction,
    TX_FROM
    from tb1
    where TX_FROM in (select TX_FROM from tb2)
    and t_n=2)
    ,

    tb4 as (select
    tb2.TX_FROM,
    avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between
    from tb2
    join tb3 on tb2.TX_FROM=tb3.TX_FROM
    group by 1)

    select
    case when time_between<1 then 'under 1 day'
    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 TX_FROM) as count_users
    from tb4
    Run a query to Download Data