mohammadhFlow vs Other L1s
    Updated 2022-07-19
    with A as (select proposer,
    Min(date_trunc('day', block_timestamp)) as min_date,
    Max(date_trunc('day', block_timestamp)) as max_date,
    count(distinct TX_ID) as tx_count
    from flow.core.fact_transactions
    group by proposer
    having Min(date_trunc('day', block_timestamp)) > '2022-4-20'),

    B as (select proposer,
    avg(datediff('day', min_date, max_date))/avg(tx_count) as Time_Between_Transactions
    from A
    group by proposer)

    select
    Case
    when TIME_BETWEEN_TRANSACTIONS < 0.25 then 'Less Than 6 hours'
    when TIME_BETWEEN_TRANSACTIONS between 0.25 and 0.5 then 'Between 6 and 12 hours'
    when TIME_BETWEEN_TRANSACTIONS between 0.5 and 1 then 'Between 12 and 24 hours'
    when TIME_BETWEEN_TRANSACTIONS between 1 and 3 then 'Between 1 and 3 days'
    when TIME_BETWEEN_TRANSACTIONS between 3 and 7 then 'Between 3 and 7 days'
    when TIME_BETWEEN_TRANSACTIONS between 7 and 14 then 'Between 1 and 2 weeks'
    when TIME_BETWEEN_TRANSACTIONS between 14 and 30 then 'Between 2 and 4 weeks'
    when TIME_BETWEEN_TRANSACTIONS > 30 then 'Greater 1 months'
    END as Time_category,
    count(proposer)

    from B
    group by Time_category