maybeyonasmantle_ethereum_beaconchain_revenue
Updated 2024-02-20
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
31
32
33
34
35
36
›
⌄
with
validators as (
select
block_timestamp,
block_number,
tx_hash,
event_index,
decoded_log:id as id,
decoded_log:operatorID as operator_id,
decoded_log:pubkey as pubkey
from ethereum.core.ez_decoded_event_logs
where block_timestamp > '2023-10-06'
and contract_address = '0xe3cbd06d7dadb3f4e6557bab7edd924cd1489e8f'
and topics[0] = '0x15f16c2e13e50235799a97b981bf4a66c8cd86051f06aca745c5ff26f39b330e'
),
validator_bals as (
select --distinct
v.slot_number,
v.index,
v.balance,
v.effective_balance,
to_timestamp_ntz((v.slot_number*12)+1606824023) as slot_timestamp,
date_trunc('day', slot_timestamp) as slot_date
from ethereum.beacon_chain.fact_validators v
join validators p
on v.pubkey = p.pubkey
and slot_timestamp >= p.block_timestamp
),
withdrawals as (
select
*,
coalesce(lag(date) over(
partition by validator_index
order by date desc
),current_date) as next_date
QueryRunArchived: QueryRun has been archived