connorhUniswap WBTC Volume
    Updated 2021-11-11
    WITH outflows AS (

    SELECT from_address_name AS pool_address, DATE_TRUNC('day',block_timestamp) AS date, symbol, SUM(AMOUNT) AS amount, SUM(AMOUNT_USD) AS amount_usd
    FROM gold.ethereum_events
    WHERE from_address_name ~ '(USDC).+ LP$'
    AND block_timestamp >= CURRENT_DATE - INTERVAL '1 month'
    AND symbol = 'USDC'
    AND to_address <> '0x0000000000000000000000000000000000000000'
    GROUP BY 1,2,3

    ), inflows AS (

    SELECT to_address_name AS pool_address, DATE_TRUNC('day',block_timestamp) AS date, symbol, SUM(AMOUNT) AS amount, SUM(AMOUNT_USD) AS amount_usd
    FROM gold.ethereum_events
    WHERE to_address_name ~ '(USDC).+ LP$'
    AND block_timestamp >= CURRENT_DATE - INTERVAL '1 month'
    AND symbol = 'USDC'
    AND from_address <> '0x0000000000000000000000000000000000000000'
    GROUP BY 1,2,3

    )

    SELECT
    inflows.date,
    SUM(inflows.amount + outflows.amount) AS wbtc_vol24hr,
    SUM(inflows.amount_usd + outflows.amount_usd) AS usd_vol24hr
    FROM inflows FULL OUTER JOIN outflows
    ON inflows.symbol = outflows.symbol AND inflows.date = outflows.date
    GROUP BY 1
    ORDER BY 1 DESC
    Run a query to Download Data