charliemarketplaceARB Claimers Categorized
Updated 2023-04-21
999
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
›
⌄
-- forked from ARB_CLAIM_CATEGORIES @ https://flipsidecrypto.xyz/edit/queries/86fafe5e-c14a-484c-a3bc-7b7d3d01e20f
-- forked from failed_claims @ https://flipsidecrypto.xyz/edit/queries/851dec24-3155-485b-971b-9bdca321019b
-- Users that successfully claimed ARB from the main contract
with successful_claims AS (
SELECT
TO_ADDRESS as recipient,
SYMBOL,
SUM(AMOUNT) as amount_claimed
FROM arbitrum.core.ez_token_transfers
WHERE CONTRACT_ADDRESS = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548')
AND FROM_ADDRESS = '0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9'
GROUP BY recipient, symbol
),
-- getting all these claimers ARB transfers
claimer_transfers AS (
SELECT
att.BLOCK_NUMBER,
att.BLOCK_TIMESTAMP,
att.TX_HASH,
FROM_ADDRESS as claimer,
amount_claimed,
att.TO_ADDRESS as drop_receiver,
COALESCE(AMOUNT,0) as amount_sent
FROM
arbitrum.core.ez_token_transfers att RIGHT JOIN successful_claims sc ON
att.FROM_ADDRESS = sc.recipient
WHERE contract_address = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548')
),
claimer_receives AS (
SELECT
att.BLOCK_NUMBER,
att.BLOCK_TIMESTAMP,
att.TX_HASH,