mboveiriMov Miner 3
Updated 2022-09-17
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
›
⌄
with top_miners as (
select
miner,
case when address_name is null then miner else address_name end as address_name,
count(block_number) as block_mined,
rank() over (order by block_mined desc) as rank
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
),
active as
(select
distinct a.miner as address
from top_miners a join ethereum.core.fact_blocks b on a.miner = b.miner
where block_timestamp >= '2022-09-01'
having a.rank <= 30
)
select
count(address_name),
case when miner in (select address from active) then 'Remain Active'
else 'Stopping Activity' end as type
from top_miners where rank <=30
group by type
Run a query to Download Data