banbannardFlash Bounty: Miners Moving? 3
Updated 2022-09-16Copy Reference Fork
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
29
30
›
⌄
⌄
with base as (select
date_trunc('week', block_timestamp) as week,
count(distinct(block_number)) as block_count,
1 as numbering
from ethereum.core.fact_blocks
where block_timestamp >= '{{start_date}}'
and block_timestamp <= '2022-09-11'
group by 1)
--having count(distinct(block_number)) > 100)
select *, (select sum(block_count)/count(distinct(week)) from base) as avg_block_count from base
/*select
case
when avg_tx_count >= 0 and avg_tx_count <= 50 then '1.AVG TX Count >= 0 and <= 50'
when avg_tx_count > 50 and avg_tx_count <= 100 then '2.AVG TX Count > 50 and <= 100'
when avg_tx_count > 100 and avg_tx_count <= 150 then '3.AVG TX Count > 100 and <= 150'
when avg_tx_count > 150 and avg_tx_count <= 200 then '4.AVG TX Count > 150 and <= 200'
when avg_tx_count > 200 and avg_tx_count <= 250 then '5.AVG TX Count > 200 and <= 250'
else '6.AVG TX Count > 250'
end as categories,
(select (avg(avg_tx_count)) from base) as avg_tx_count_per_miner,
(select count(distinct(miner)) from base) as miner_count,
case
when avg_tx_count >= avg_tx_count_per_miner then 'Miners with Above Average TX Count'
when avg_tx_count <= avg_tx_count_per_miner then 'Miners with Below Average TX Count'
end as categories2,
count(distinct(miner)) as count_miners
from base
group by 1,4*/
Run a query to Download Data