angeal2023-06-23 10:15 AM
Updated 2023-07-23
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 recipients AS (select
block_timestamp as claim_time,
to_address as recipient,
amount AS airdrop_amount
from
optimism.core.ez_token_transfers
where
contract_address = lower('0x4200000000000000000000000000000000000042') --OP token
AND from_address = lower('0xfedfaf1a10335448b7fa0268f56d2{{param_5Yh1}}b44dbd357de') --claim contract
AND block_timestamp > '2023-01-01'),
swaps AS (
SELECT
sender AS airdrop_recipient,
sum(amount_in) AS airdrop_swapped
FROM
optimism.core.ez_dex_swaps ds
JOIN recipients rr ON ds.sender = rr.recipient
WHERE
token_in = lower('0x4200000000000000000000000000000000000042')
AND
ds.block_timestamp > rr.claim_time
GROUP BY sender
)
SELECT
recipient,
airdrop_amount,
airdrop_swapped,
IFF(airdrop_swapped > airdrop_amount, 1, coalesce(airdrop_swapped, 0)/airdrop_amount) AS prop_swapped
FROM
recipients r
LEFT JOIN swaps s ON r.recipient = s.airdrop_recipient
Run a query to Download Data