miner_cte as (

select

BLOCK_TIMESTAMP::date date

, miner

from ethereum.core.fact_blocks

where 1=1

and date between '2022-01-01' and '2022-09-14'

)

, jul_sep_cte as (

select

distinct

miner

from miner_cte

where date between '2022-07-01' and '2022-09-14'

)

, aug_sep_cte as (

select

distinct

miner

from miner_cte

where date between '2022-08-05' and '2022-09-14'

)

select

'Period' period,

count(a.miner) "[Jul 1 - Sep 14 2022] miner"

, count(case when b.miner is not null then a.miner end) "[Aug 5 - Sep 14 2022] miner"

, count(case when b.miner is null then a.miner end) "Miner stopped mining after Aug 4"

from jul_sep_cte a

left join aug_sep_cte b using(miner)