SocioCryptoAverage hours between two transactions - bots excluded
    Updated 2022-07-20
    WITH payers as (SELECT *,
    rank()over(partition by payer ORDER BY block_timestamp) as rank
    FROM flow.core.fact_transactions
    WHERE block_timestamp::date >= '2022-04-20'
    AND payer NOT IN (
    SELECT DISTINCT trader
    FROM
    (
    SELECT trader,
    date_trunc('hour',block_timestamp) as hours,
    count(tx_id) as n_swaps
    FROM flow.core.fact_swaps
    GROUP BY trader,hours
    )
    WHERE n_swaps >= 30
    )
    ),
    address as (SELECT *,
    rank()over(partition by from_address ORDER BY block_timestamp) as rank
    FROM ethereum.core.fact_transactions
    WHERE block_timestamp::date >= '2022-04-20'
    AND from_address NOT IN (
    SELECT DISTINCT sender
    FROM
    (
    SELECT sender,
    date_trunc('hour',block_timestamp) as hours,
    count(tx_hash) as n_swaps
    FROM ethereum.core.ez_dex_swaps
    GROUP BY sender,hours
    )
    WHERE n_swaps >= 30
    )
    )

    SELECT avg(datediff (hour,a.block_timestamp, b.block_timestamp)) as period
    Run a query to Download Data