purifBeradrome delegators
    Updated 2024-09-02
    with data as (
    select day, sum(amount) as delegated, count(tx_hash) as delegations from (
    select date_trunc('day',block_timestamp) as day, concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount, tx_hash
    from berachain.testnet.fact_event_logs
    where ORIGIN_TO_ADDRESS='0xbda130737bdd9618301681329bf2e46a016ff9ad'
    and contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'
    and ORIGIN_FUNCTION_SIGNATURE='0x95c0e232'
    and concat('0x',substr(TOPICS[2], 27,64))='0x34d023aca5a227789b45a62d377b5b18a680be01'
    order by BLOCK_TIMESTAMP desc)
    group by 1
    ),
    unbond as (
    select day, sum(unbonded) as unbonded from (
    select date_trunc('day',block_timestamp) as day, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as unbonded, tx_hash
    from berachain.testnet.fact_event_logs
    where ORIGIN_TO_ADDRESS='0xbda130737bdd9618301681329bf2e46a016ff9ad'
    and contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'
    and ORIGIN_FUNCTION_SIGNATURE='0xe1f63d2e'
    and concat('0x',substr(TOPICS[2], 27,64))='0x34d023aca5a227789b45a62d377b5b18a680be01'
    order by block_timestamp desc)
    group by 1
    ),

    daily_data as (select coalesce(d.day,u.day) as day, coalesce(delegated,0) as delegated, coalesce(unbonded,0) as unbonded, delegations from data d
    left join unbond u on u.day=d.day)
    select day, delegated,unbonded, delegations, delegated-unbonded as net_delegation,
    sum(delegated-unbonded) over (order by day) as cumulative_amount,
    sum(delegations) over (order by day) as cumulative_count
    from daily_data
    order by day desc


    QueryRunArchived: QueryRun has been archived