distinct miner as new_miner,

min(block_timestamp) as min_date

from ethereum.core.fact_blocks

group by 1

having min_date between '2017-09-16' and '2022-09-15'

select

date_trunc('week' , min_date::date) as date,

count(new_miner) as count,

case

when date between '2022-06-02' and '2022-06-20' then 'Merge comes to Ropsten'

when date between '2022-06-20' and '2022-08-04' then 'Merge comes to Sepolia'

when date > '2022-08-04' then 'Merge comes to Goerli'

when date = '2022-09-14' then 'Mainnet Merge'

else 'PoW'

end as type

from new_miners

group by 1

order by date