with data as (select
date_trunc('day', block_timestamp) as day,
count(distinct(block_number)) as blocks_mined,
miner
from ethereum.core.fact_blocks
where block_timestamp >= current_date() - 30
and block_timestamp <= '2022-9-14'
group by miner, day)
select
day,
avg(blocks_mined) as avg_blocks_mined_per_miner,
median(blocks_mined) as median_blocks_mined_per_miner
from data
group by day