Crazzy_SidETH: Median Transaction Gas Limit
    Updated 2024-10-12
    WITH GasLimitRanks AS (
    SELECT
    DATE(BLOCK_TIMESTAMP) AS date,
    GAS_LIMIT,
    ROW_NUMBER() OVER (PARTITION BY DATE(BLOCK_TIMESTAMP) ORDER BY GAS_LIMIT) AS rn,
    COUNT(*) OVER (PARTITION BY DATE(BLOCK_TIMESTAMP)) AS total_count
    FROM
    ethereum.core.fact_transactions
    WHERE
    BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '30 DAY'
    AND STATUS = 'SUCCESS'
    )
    SELECT
    date,
    AVG(GAS_LIMIT) AS median_gas_limit
    FROM
    GasLimitRanks
    WHERE
    rn IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2))
    GROUP BY
    date
    ORDER BY
    date DESC;

    QueryRunArchived: QueryRun has been archived