select

(CASE

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

WHEN block_timestamp >= '2024-03-14 15: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 arbitrum.core.fact_transactions

WHERE block_timestamp BETWEEN

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

AND

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

GROUP BY 1

),

before AS(

SELECT avg_l2_fee,transactions FROM datas

WHERE label = 'Before Dencun'

),

after AS(

SELECT avg_l2_fee,transactions FROM datas

WHERE label = 'After Dencun'

SELECT

after.avg_l2_fee AS after_fee,

before.avg_l2_fee AS before_fee,

((before.avg_l2_fee - after.avg_l2_fee )/before.avg_l2_fee) * 100 AS Total_Saving_Percentage,