Ramahar$OP airdrop user behavior
Updated 2022-11-13Copy 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
›
⌄
/* Query referring to https://app.flipsidecrypto.com/dashboard/op-airdrop-NMHf05 submision */
/*Ammended accordingly*/
WITH claimers AS ( SELECT
to_address AS address,
SUM(raw_amount) / POW(10, 18) AS amount
FROM optimism.core.fact_token_transfers
WHERE from_address = '0xfedfaf1a10335448b7fa0268f56d2b44dbd357de' AND contract_address = '0x4200000000000000000000000000000000000042' AND origin_function_signature = '0x2e7ba6ef'
GROUP BY 1
),
receivers AS ( SELECT
claimers.address,
SUM(transfers.raw_amount) / POW(10, 18) AS amount
FROM optimism.core.fact_token_transfers transfers
JOIN claimers
ON transfers.to_address = claimers.address
WHERE contract_address = '0x4200000000000000000000000000000000000042'
GROUP BY 1
),
senders AS ( SELECT
claimers.address,
SUM(transfers.raw_amount) / POW(10, 18) AS amount
FROM optimism.core.fact_token_transfers transfers
JOIN claimers
ON transfers.from_address = claimers.address
WHERE contract_address = '0x4200000000000000000000000000000000000042'
GROUP BY 1
),
holders AS (
SELECT
claimers.address,
claimers.amount AS claimed,
receivers.amount AS received,
senders.amount AS sent,
(receivers.amount - ZEROIFNULL(senders.amount)) AS holdings,
CASE
WHEN holdings > claimed THEN 'Accumulators'
Run a query to Download Data