purifBadge minters filtering
Updated 2024-08-28
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 badge_minters as (
select
distinct minter as minter,
count(badge_id) as minted_badges
from
(
select
decoded_log ['to'] as minter,
decoded_log ['id'] as badge_id
from
berachain.testnet.fact_decoded_event_logs
where
CONTRACT_ADDRESS = lower('0x886D2176D899796cD1AfFA07Eff07B9b2B80f1be')
and decoded_log ['from'] = lower('0x0000000000000000000000000000000000000000')
)
group by 1
),
--every users BGT balance
bgt_balance as (
select
user,
sum(value) as balance
from
(
--claim bgt
select
concat('0x', substr(TOPICS [2], 27, 64)) as user,
(ethereum.public.udf_hex_to_int(data) :: int) / 1e18 as value
from
berachain.testnet.fact_event_logs
where
CONTRACT_ADDRESS = lower('0xbDa130737BDd9618301681329bF2e46A016ff9Ad')
and topics [0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
UNION ALL
--redeem bgt
select
QueryRunArchived: QueryRun has been archived