niloalgo
    Updated 2022-12-20
    --credit to: Ali3N

    with flowtable1 as (
    select TX_SENDER,
    TX_GROUP_ID,
    block_timestamp,
    row_number () over (partition by TX_SENDER order by block_timestamp) as RN
    from algorand.core.fact_transaction
    where block_timestamp >= CURRENT_DATE - interval'6 months'),
    flowtable2 as (
    select TX_SENDER,
    TX_GROUP_ID,
    block_timestamp,
    row_number () over (partition by TX_SENDER order by block_timestamp) as RN
    from algorand.core.fact_transaction
    where block_timestamp >= CURRENT_DATE - interval'6 months'),

    flowtable3 as (
    select t1.TX_SENDER,
    avg (timediff (hour,t1.block_timestamp,t2.block_timestamp)) as Time_Difference
    from flowtable1 t1 join flowtable2 t2 on t1.TX_SENDER = t2.TX_SENDER and t2.rn = t1.rn + 1
    group by 1)

    select case
    when Time_Difference < 24 then 'Less than One Day'
    when Time_Difference >= 24 and Time_Difference < 48 then 'Between 1 and 2 Days'
    when Time_Difference >= 48 and Time_Difference < 168 then 'Between 2 and 7 Days'
    when Time_Difference >= 168 and Time_Difference < 336 then 'Between 1 Week and 2 Weeks'
    when Time_Difference >= 336 and Time_Difference < 720 then 'Between 2 Weeks and 1 Month'
    when Time_Difference >= 720 and Time_Difference < 2160 then 'Between 1 and 3 Months'
    when Time_Difference >= 2160 then 'More than 3 Months' end as Time_Difference1,
    count (*)
    from flowtable3
    group by 1
    order by 2 desc
    Run a query to Download Data