CryptoLionTop 5 Pools
    Updated 2023-03-26
    WITH top as (
    SELECT
    pool_name,
    sum(abs(amount0_usd)) as v_volume
    FROM uniswapv3.swaps
    WHERE block_timestamp >= getdate() - interval '7 days'
    GROUP BY 1
    HAVING v_volume IS NOT NULL
    ORDER BY 2 DESC
    LIMIT 5
    )

    SELECT
    date_trunc('week',block_timestamp) as week,
    uniswapv3.swaps.pool_name,
    sum(abs(amount0_usd)) as volume,
    LAG(volume) IGNORE NULLS OVER (PARTITION BY uniswapv3.swaps.pool_name ORDER BY week) as lvolume,
    volume - lvolume as change_volume
    FROM uniswapv3.swaps
    INNER JOIN top on top.pool_name = uniswapv3.swaps.pool_name
    WHERE block_timestamp >= getdate() - interval '10 weeks'
    GROUP BY 1,2
    ORDER BY 1 DESC
    Run a query to Download Data