lanoweekly-tx
    Updated 2024-09-09
    WITH TX AS
    (SELECT
    date_trunc('week', block_timestamp) AS date,
    from_address,
    COUNT(tx_hash) AS cnt
    FROM
    kaia.core.fact_transactions
    WHERE block_timestamp > CURRENT_DATE() - 90
    GROUP BY
    date, from_address;
    ),
    avg_tx as
    (
    select
    from_address,
    round(sum(cnt)/(select count(distinct date_trunc('week', date))
    from tx)) as cnt
    from tx
    group by 1
    )

    SELECT
    CASE
    WHEN cnt >= 500 THEN 500
    ELSE cnt
    END AS adjusted_cnt,
    COUNT(*) AS addresses
    FROM
    avg_Tx
    GROUP BY
    adjusted_cnt;



    QueryRunArchived: QueryRun has been archived