theericstoneTotal swaps by pool in Uniswap v3
    Updated 2023-03-10
    -- This query is for the Daily swap based on the group of pool, including the
    WITH prices AS (
    SELECT * FROM ethereum.token_prices_hourly
    ),
    swap_raw AS (
    SELECT
    date_trunc('hour', swaps.block_timestamp) AS block_hour,
    pool_name,
    token0_symbol,
    token1_symbol,
    amount0_adjusted,
    amount1_adjusted,
    -- CASE WHEN amount0_adjusted < 0 THEN 'out' ELSE 'in' END AS token0_direction,
    -- CASE WHEN amount1_adjusted < 0 THEN 'out' ELSE 'in' END AS token1_direction,
    CASE WHEN amount0_adjusted < 0 THEN amount0_adjusted ELSE amount1_adjusted END AS swap_amount_out,
    CASE WHEN amount0_adjusted >= 0 THEN amount0_adjusted ELSE amount1_adjusted END AS swap_amount_in,
    CASE WHEN amount0_adjusted < 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_out,
    CASE WHEN amount0_adjusted >= 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_in
    FROM uniswapv3.swaps swaps
    LEFT JOIN prices p0
    ON p0.hour = date_trunc('hour', swaps.block_timestamp) AND p0.token_address = swaps.token0_address
    LEFT JOIN prices p1
    ON p1.hour = date_trunc('hour', swaps.block_timestamp) AND p1.token_address = swaps.token1_address
    where swaps.block_timestamp > getdate() - interval '7 days'
    )

    SELECT * from (
    SELECT
    pool_name,
    token0_symbol,
    token1_symbol,
    SUM(swap_amount_in) AS swap_amount_in,
    SUM(swap_amount_out) AS swap_amount_out,
    SUM(swap_usd_amount_in) AS swap_usd_amount_in,
    SUM(swap_usd_amount_out) AS swap_usd_amount_out
    FROM swap_raw
    Run a query to Download Data