MLDZMNaverage time ETH
    Updated 2022-06-21
    with tb1 as (select
    BLOCK_TIMESTAMP,
    FROM_ADDRESS,
    ROW_NUMBER() OVER (partition by FROM_ADDRESS order by BLOCK_TIMESTAMP) as t_n
    from ethereum.core.fact_transactions
    where STATUS='SUCCESS'
    ),

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

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

    tb4 as (select
    tb2.FROM_ADDRESS,
    avg(DATEDIFF(day,first_transaction, second_transaction )) as time_between
    from tb2
    join tb3 on tb2.FROM_ADDRESS=tb3.FROM_ADDRESS
    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 FROM_ADDRESS) as count_users
    Run a query to Download Data