kellenUNI Airdrops vs Price
Updated 2021-07-30Copy 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
›
⌄
WITH airdrop AS (
SELECT DATE_TRUNC(day, block_timestamp) AS date
, COUNT(DISTINCT to_address) AS n_address
, SUM(amount) AS airdrop_amt
FROM ethereum.udm_events
WHERE 1=1
AND from_address = '0x090d4613473dee047c3f2706764f49e0821d256e'
AND contract_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'
AND amount >= 400
-- AND block_timestamp > '2020-09-16 21:32:51.000'
-- AND block_timestamp < '2020-11-01'
AND origin_function_name = 'claim'
GROUP BY 1
), price AS (
SELECT DATE_TRUNC(day, hour) AS date
, price
, ROW_NUMBER() OVER (PARTITION BY date ORDER BY hour DESC) AS rn
FROM ethereum.token_prices_hourly_v2
WHERE token_address = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'
), price_clean AS (
SELECT date
, price
, LAG(price, 1) OVER (ORDER BY date ASC) AS prv_price
, LAG(price, 1) OVER (ORDER BY date DESC) AS nxt_price
FROM price
WHERE rn = 1
)
SELECT p.*
, COALESCE(a.n_address, 0) AS n_address
, COALESCE(a.airdrop_amt, 0) AS airdrop_amt
, SUM(COALESCE(a.airdrop_amt, 0)) OVER (ORDER BY p.date) AS cum_airdrop_amt
FROM price_clean p
LEFT JOIN airdrop a ON p.date = a.date
ORDER BY p.date
Run a query to Download Data