purifDelegators list
Updated 2024-10-17
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 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