CryptoLionscratch
    Updated 2021-06-07
    -- A
    -- Current Top 5 liquidity pools FROM pool_stats
    -- Key columns: pool_name, liquidity
    WITH a as (
    SELECT DISTINCT
    date_trunc('day', ps1.block_timestamp) as day,
    pool_name,
    last_value(token0_balance_usd + token1_balance_usd) over (partition by pool_name order by day) as liquidity,
    last_value(virtual_liquidity_adjusted) over (partition by pool_name order by day) as vliquidity
    FROM uniswapv3.pool_stats ps1
    WHERE block_timestamp >= getdate() - interval '1 day'
    AND token0_balance_usd >= 0
    AND token1_balance_usd >= 0
    --
    -- AND pool_name LIKE 'WBTC-WETH%'
    --
    HAVING day = '2021-06-06'
    ORDER BY 3 DESC
    LIMIT 5
    )

    SELECT
    pc.pool_name,
    pc.liquidity_provider,
    SUM(pc.amount0_usd + pc.amount1_usd) as fees
    FROM uniswapv3.position_collected_fees pc
    INNER JOIN a on a.pool_name = pc.pool_name
    GROUP BY 1,2
    HAVING fees > 0
    ORDER BY 3 DESC