purifDelegators list
    Updated 2024-10-17
    with delegate as (
    select concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount
    from berachain.testnet.fact_event_logs
    where contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'
    and topics[0]='0x99966631dd6d6c02c5416ca2369709e025ff974a2f1b3f11c8b74acc67731f0e'
    and concat('0x',substr(TOPICS[2], 27,64))=lower('{{validator_address}}')
    ),
    unbond as (
    select concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount
    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))=lower('{{validator_address}}')
    ),
    sum_delegated as (
    SELECT address, sum(amount) as d FROM delegate
    group by 1
    ),
    sum_unbound as (
    SELECT address, sum(amount) as u FROM unbond
    group by 1
    )

    select address, amount as delegated,
    sum(delegated) over (order by delegated) as cum_delegated,
    count(address) over (order by delegated) as delegators
    from
    (select sd.address, d-coalesce(u,0) as amount
    from sum_delegated sd
    left join sum_unbound su on sd.address=su.address)
    where amount > 1
    order by amount desc



    QueryRunArchived: QueryRun has been archived