kellenUNI Airdrops vs Price
    Updated 2021-07-30
    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