WITH gas_prices AS (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) as time,
AVG(gas_price/1e9) as avg_gas_price,
max(gas_price/1e9) as max_fee_price_max
FROM avalanche.core.fact_transactions
GROUP BY time
),
Blocks AS (
SELECT
date_trunc('day', BLOCK_TIMESTAMP) as time,
AVG(gas_limit) AS avg_block_gas_limit,
SUM(gas_used) as total_gas_used
FROM avalanche.core.fact_blocks
GROUP BY 1
)
SELECT
A.time,
avg_gas_price AS "AVG gas price [Gwei]",
max_fee_price_max AS "MAX gas price [Gwei]",
avg_block_gas_limit AS "Average Gas Limit",
total_gas_used AS "Total Gas Used"
FROM gas_prices A
LEFT JOIN Blocks B ON A.time = B.time
ORDER BY 1 desc