kellenFEI Airdrop Outcome Timeline
Updated 2021-08-10Copy Reference Fork
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
›
⌄
WITH constants AS (
SELECT '0x956f47f50a910163d8bf957cf5846d573e7f87ca' AS c_contract_address
, '0xbffb152b9392e38cddc275d818a3db7fe364596b' AS c_from_address
, 'redeem' AS c_origin_function_name
), airdrop_recipients AS (
SELECT to_address AS address
, MIN(block_timestamp) AS rec_time
, SUM(amount) AS airdrop_amt
FROM ethereum.udm_events u
JOIN constants c ON c.c_contract_address = u.contract_address
AND c.c_origin_function_name = u.origin_function_name
AND c.c_from_address = u.from_address
GROUP BY 1
), transfers_to_0 AS (
SELECT to_address AS address
, SUM(amount) AS transfer_to_amt
, SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS transfer_to_amt_24h
, SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS transfer_to_amt_7d
FROM ethereum.udm_events u
JOIN airdrop_recipients r ON r.address = u.to_address
AND u.block_timestamp > r.rec_time
JOIN constants c ON c.c_contract_address = u.contract_address
WHERE from_label IS NULL
GROUP BY 1
), transfers_from_0 AS (
SELECT from_address AS address
, SUM(amount) AS transfer_from_amt
, SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS transfer_from_amt_24h
, SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS transfer_from_amt_7d
FROM ethereum.udm_events u
JOIN constants c ON c.c_contract_address = u.contract_address
JOIN airdrop_recipients r ON r.address = u.from_address
AND u.block_timestamp > r.rec_time
WHERE to_label IS NULL
GROUP BY 1
), cex_transfers_to_0 AS (
Run a query to Download Data