tab1 as (

SELECT

trunc(block_timestamp,'day') as time,

case when time<'2022-09-15' then 'Before merge'

when time = '2022-09-15' then 'Merge day'

else 'After merge' end as period,

count(DISTINCT miner) as active_miners,

avg(tx_count) as tx_per_block

FROM ethereum.core.fact_blocks

where block_timestamp >='2022-09-01'

GROUP BY 1 ,2 order by 1

),

tab2 as (

SELECT

avg(active_miners) as avg_active_miners,

avg(tx_per_block) as avg_tx_per_block

from tab1

time,period,

avg_active_miners,

avg_tx_per_block,

active_miners,

tx_per_block

from tab1 join tab2

order by 1