purifBeradrome delegators list copy
Updated 2024-07-31
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
›
⌄
-- forked from Beradrome delegators list @ https://flipsidecrypto.xyz/studio/queries/f2bdbea6-6b55-47ba-ba80-034848bbf59f
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 ORIGIN_TO_ADDRESS='0xbda130737bdd9618301681329bf2e46a016ff9ad'
and contract_address='0xbda130737bdd9618301681329bf2e46a016ff9ad'
and ORIGIN_FUNCTION_SIGNATURE='0x95c0e232'
and concat('0x',substr(TOPICS[2], 27,64))='0x34d023aca5a227789b45a62d377b5b18a680be01'),
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))='0x34d023aca5a227789b45a62d377b5b18a680be01'
),
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, d, u from
(select sd.address, d-coalesce(u,0) as amount, d, u
from sum_delegated sd
left join sum_unbound su on sd.address=su.address)
where amount >= 10
QueryRunArchived: QueryRun has been archived