SELECT

date_trunc('hour', block_timestamp) AS hour,

avg(tx_fee) AS avg_fee,

sum(tx_fee) AS total_hourly_fee

FROM polygon.core.fact_transactions

WHERE status = 'SUCCESS'

AND block_timestamp BETWEEN '2022-07-01 00:00:00' AND '2022-07-08 23:59:59'

GROUP BY 1

),

eth_fees AS (

SELECT

date_trunc('hour', block_timestamp) AS hour,

avg(tx_fee) AS avg_fee,

sum(tx_fee) AS total_hourly_fee

FROM ethereum.core.fact_transactions

WHERE status = 'SUCCESS'

AND block_timestamp BETWEEN '2022-07-01 00:00:00' AND '2022-07-08 23:59:59'

GROUP BY 1

SELECT

p.hour AS "Hour",

p.avg_fee AS "Polygon average fee (MATIC)",

e.avg_fee AS "Ethereum average fee (ETH)"

FROM poly_fees p

JOIN eth_fees e on p.hour = e.hour

ORDER BY 1;