DISTINCT miner

from ethereum.core.fact_blocks

where network='mainnet'

)

select

'before Merge' as period,

count(DISTINCT origin_from_address) as "count of miners",

count(DISTINCT tx_hash) as "count of transaction",

sum(amount) as "volume (ETH)"

from ethereum.core.ez_eth_transfers a join ethereum.core.dim_labels b on a.ORIGIN_TO_ADDRESS = b.address

where block_timestamp::date between '2022-09-01' and '2022-09-15 06:46:00.000'

and origin_from_address in (SELECT miner from main)

group by 1

union

select

'after Merge' as period,

count(DISTINCT origin_from_address) as "count of miners",

count(DISTINCT tx_hash) as "count of transaction",

sum(amount) as "volume (ETH)"

from ethereum.core.ez_eth_transfers a join ethereum.core.dim_labels b on a.ORIGIN_TO_ADDRESS = b.address

where block_timestamp::date > '2022-09-15 06:46:00.000'

and origin_from_address in (SELECT miner from main)

group by 1