WITH
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-01-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
)
SELECT
time,period,
avg_active_miners,
avg_tx_per_block,
active_miners,
tx_per_block
from tab1 join tab2
order by 1