kellenFEI Airdrop Outcome Timeline
    Updated 2021-08-10
    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