select

miner,

case when address_name is null then miner else address_name end as address_name,

count(block_number) as block_mined

from ethereum.core.fact_blocks left join ethereum.core.dim_labels on miner = address

where year(block_timestamp) >= '2022' and block_timestamp < '2022-09-01'

group by 1,2

having block_mined > 1000

order by block_mined desc

limit 50

select

date_trunc('month',block_timestamp::date) as date,

case when address_name is null then miner else address_name end as address_name,

count(*) as count,

count(block_number) as block_count,

(select count(distinct miner) from ethereum.core.fact_blocks where year(block_timestamp) = '2022') as miners_count,

(select count(block_number) from ethereum.core.fact_blocks where year(block_timestamp) = '2022') as blocks_mined,

(select sum(tx_count) from ethereum.core.fact_blocks where year(block_timestamp) = '2022') as total_transactions,

(select avg(tx_count) from ethereum.core.fact_blocks where year(block_timestamp) = '2022') as total_avereage

from ethereum.core.fact_blocks left join ethereum.core.dim_labels on miner = address

where year(date) = '2022' and date <= '2022-09-15'

and miner in (select miner from top_miners)

group by 1,2