CryptoLionTop 5 Pools liq
    Updated 2023-03-27
    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
    ),
    liq as (
    SELECT
    date_trunc('week',block_timestamp) as week,
    uniswapv3.lp_actions.pool_name,
    SUM(CASE WHEN action = 'INCREASE_LIQUIDITY' THEN amount0_usd+amount1_usd ELSE 0 END) as add_liq,
    SUM(CASE WHEN action = 'DECREASE_LIQUIDITY' THEN amount0_usd+amount1_usd ELSE 0 END) as sub_liq
    FROM uniswapv3.lp_actions
    INNER JOIN top on top.pool_name = uniswapv3.lp_actions.pool_name
    WHERE block_timestamp >= getdate() - interval '10 weeks'
    GROUP BY 1,2
    ORDER BY 1 DESC
    )

    SELECT
    week,
    pool_name,
    add_liq-sub_liq as liq_volume,
    LAG(liq_volume) IGNORE NULLS OVER (ORDER BY week) as last_liq,
    liq_volume-last_liq as change_liq
    FROM liq
    GROUP BY 1,2,3
    ORDER BY 1 DESC