select '{{aggregation_period}}' as aggregation_period,

date_trunc('{{aggregation_period}}', block_timestamp) as time_unit,

case

when block_number between 1 and 3218635 then 'Before Gas Limit Halving: 300M gas limit'

when block_number between 3218636 and 3982324 then 'After Limit Halving and Testnet Launch'

when block_number > 3982324 then 'After launch'

end as interval_name,

case

-- when aggregation_period = 'second' then sum(tx_count) / (60 * 60 * 60 * 60)

when aggregation_period = 'minute' then sum(tx_count) / (60 )

when aggregation_period = 'hour' then sum(tx_count) / (60*60)

when aggregation_period = 'day' then sum(tx_count) / (24*60*60)

when aggregation_period = 'week' then sum(tx_count) / (7*24*60*60)

-- when aggregation_period = 'month' then sum(tx_count) / (30*7*24*60*60)

end as transactions_per_second,

avg(gas_used / nullif(gas_limit,0)) as block_utilization_rate,

sum(gas_limit) as total_gas_limit,

sum(tx_count) as tx_count,

sum (gas_used * 50) as total_tx_fees,

sum(gas_used) as total_gas_used,

avg(tx_count) as avg_txs,

avg(gas_used*50) as avg_tx_fee,

avg(gas_used) as avg_gas_used,

avg(block_number) as avg_blocks

from monad.testnet.fact_blocks

where block_number <> 0

and block_timestamp between '2025-02-07 17:08:00.000' and '2025-02-07 17:38:00.000'

--and block_timestamp between '2025-02-07 16:10:00.000' and '2025-02-07 16:47:00.000'

-- and block_timestamp between '2025-02-07 15:59:00.000' and '2025-02-07 18:00:00.000'

-- and block_timestamp between '2025-01-24 16:00' and '2025-01-24 17:59'

-- and block_timestamp between '2025-01-24 15:59:00.000' and '2025-01-24 17:20:00.000'

group by 1,2,3

),

final as (

select time_unit as {{aggregation_period}},