purifBeradrome delegators
Updated 2024-09-02
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
›
⌄
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