connorhETH Use after ALCX-ETH Swaps
    Updated 2021-11-11
    -- ALCX SLP swap volume/fees: using the ethereum.events_emitted table
    WITH swaps_hourly AS(
    -- use the events_emitted table to parse swaps
    -- example: https://etherscan.io/tx/0x76e3af1083e5bb2f3aa87b974dc3c3a81e7aa8eb4830c69b0456f1a8a4d00038#eventlog
    SELECT tx_to_address AS swapper,
    DATE_TRUNC('hour',block_timestamp) AS swap_date, contract_address AS pool_address,
    SUM(event_inputs:amount1In) AS amountIn,
    MIN(block_timestamp) AS first_swap
    FROM ethereum.events_emitted
    WHERE block_timestamp >= CURRENT_DATE - interval '4 months' AND block_timestamp <= CURRENT_DATE - interval '1 day'
    AND contract_address = '0xc3f279090a47e80990fe3a9c30d24cb117ef91a8' -- WETH-ALCX SLP
    AND event_name = 'Swap'
    GROUP BY 1,2,3
    HAVING amountin > 0
    )

    SELECT
    CASE WHEN to_label IS NULL THEN 'Other Users' ELSE to_label END AS to_label,to_address,
    symbol,SUM(amount) AS amount,SUM(amount_usd) AS amount_usd,COUNT(tx_id) AS N_TXN
    FROM
    ethereum.udm_events
    WHERE contract_address = LOWER('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') AND from_address IN (SELECT swapper FROM swaps_hourly)
    AND block_timestamp >= CURRENT_DATE - interval '7 days'
    GROUP BY 1,2,3
    ORDER BY AMOUNT DESC
    Run a query to Download Data