purifDelegators distribution by amount
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
33
›
⌄
with data as (select concat('0x',substr(TOPICS[1], 27,64)) as address, (ethereum.public.udf_hex_to_int(data)::int)/1e18 as amount,
concat('0x',substr(TOPICS[2], 27,64)) as validator
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),
delegators as (select address, amount from (
select address, sum(amount) as amount from data
group by 1)
where amount >= 1
order by amount desc)
select count(address) as wallet_amount,
CASE
WHEN amount >= 1 and amount < 10 THEN '1-10'
WHEN amount >= 10 and amount < 50 THEN '10-50'
WHEN amount >= 50 and amount < 100 THEN '50-100'
WHEN amount >= 100 and amount < 1000 THEN '100-10'
WHEN amount >= 1000 THEN '>1000'
END AS amount,
CASE
WHEN amount >= 1 and amount < 10 THEN '1'
WHEN amount >= 10 and amount < 50 THEN '2'
WHEN amount >= 50 and amount < 100 THEN '3'
WHEN amount >= 100 and amount < 1000 THEN '4'
WHEN amount >= 1000 THEN '6'
END AS "order"
from delegators
group by 2,3
order by 3
QueryRunArchived: QueryRun has been archived