with swaps AS (

SELECT tx_hash FROM base.defi.ez_dex_swaps

WHERE block_timestamp BETWEEN

DATEADD(hour, -timestampdiff('hour', '2024-03-14 00:00:00.000', current_timestamp), '2024-03-14 00:00:00.000')

AND

DATEADD(hour, timestampdiff('hour', '2024-03-14 00:00:00.000', current_timestamp), '2024-03-14 00:00:00.000')

select

date_trunc('hour',block_timestamp) AS hour,

(CASE

WHEN block_timestamp < '2024-03-14 00:00:00.000' THEN 'Before Dencun'

WHEN block_timestamp >= '2024-03-14 00:00:00.000' THEN 'After Dencun'

END

) AS label,

AVG(tx_fee) avg_l2_fee,

AVG(l1_fee) avg_l1_fee,

SUM(tx_fee) AS layer2_fee,

SUM(l1_fee) AS layer1_fee,

count(DISTINCT tx_hash) AS transactions

FROM base.core.fact_transactions

WHERE

tx_hash IN (SELECT tx_hash FROM swaps)

AND block_timestamp BETWEEN

DATEADD(hour, -timestampdiff('hour', '2024-03-14 00:00:00.000', current_timestamp), '2024-03-14 00:00:00.000')

AND

DATEADD(hour, timestampdiff('hour', '2024-03-14 00:00:00.000', current_timestamp), '2024-03-14 00:00:00.000')

GROUP BY 1,2