maybeyonasmantle_ethereum_beaconchain_revenue
    Updated 2024-02-20
    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