Alexayhold
Updated 2022-09-25Copy Reference Fork
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 op_airdrop_1_metadata AS ( SELECT 214748364.8 AS op_tokens_to_claim , 248699 AS eligible_users),
op_transfers AS ( SELECT block_timestamp::date AS utc_date , tx_hash , from_address AS wallet , -raw_amount / power(10,18) AS amount
, 'send' AS tx_type , from_address AS _from_address , to_address AS _to_address
FROM optimism.core.fact_token_transfers
WHERE contract_address = '0x4200000000000000000000000000000000000042'
AND from_address != '0x0000000000000000000000000000000000000000'
UNION ALL
SELECT block_timestamp::date AS utc_date, tx_hash , to_address AS wallet , raw_amount / power(10,18) AS amount
, 'receive' AS tx_type , from_address AS _from_address , to_address AS _to_address
FROM optimism.core.fact_token_transfers
WHERE contract_address = '0x4200000000000000000000000000000000000042'
AND to_address != '0x0000000000000000000000000000000000000000'),
op_balances AS ( SELECT wallet , greatest(0, sum(amount)) AS balance
FROM op_transfers
GROUP BY 1),
op_airdrop_claims AS ( SELECT *
FROM op_transfers
WHERE _from_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'
AND wallet != '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de'),
op_claimers AS ( SELECT c.wallet, c.amount AS claimed_amount, b.balance, least(100.000, 100.000 * b.balance / claimed_amount) AS perc_airdrop_held,
(CASE WHEN perc_airdrop_held >= 90 THEN 'Hold'
WHEN perc_airdrop_held >= 10 THEN 'Hold (Partial)'
ELSE 'Sold' END) AS _group
FROM op_airdrop_claims AS c
LEFT JOIN op_balances AS b
ON b.wallet = c.wallet)
SELECT _group, count(*) AS wallets
FROM op_claimers
Run a query to Download Data