SELECT

DATE(BLOCK_TIMESTAMP) AS date,

GAS_USED,

ROW_NUMBER() OVER (PARTITION BY DATE(BLOCK_TIMESTAMP) ORDER BY GAS_USED) 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_USED) AS median_gas_used

FROM

GasUsedRanks

WHERE

rn IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2))

GROUP BY

date

ORDER BY

date DESC;