defi__joshHidden Patterns in Ethereum Mining!
Updated 2025-03-08
999
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
31
32
33
34
35
36
›
⌄
/*
-- select *
-- from bsc.core.ez_token_transfers
-- where to_address = lower('0x07Bc3c5cb510690e5B56B9C23e4e1da27Ff3217C')
-- and block_timestamp :: date = '2024-10-14'
with raw as (
select
block_number,
miner,
lag(miner) over (order by block_number asc) as prev_miner,
lead(miner) over (order by block_number asc) as next_miner,
case
when miner = next_miner then 1
when miner = prev_miner then 1
else 0
end as consecutive_tag,
from ethereum.core.fact_blocks
--where block_number between 69563 and 69566
)
-- select * from raw
-- where block_number between 69563 and 69566
,
block_number_tag as (
select
*,
iff(consecutive_tag = 1 and miner!= next_miner , block_number, null ) as block_num_tag,
from raw
)
QueryRunArchived: QueryRun has been archived