maybeyonastop_10_lp-top_10_pools
    Updated 2021-09-29
    WITH top5_pools as(
    SELECT
    st.block_timestamp as date,
    st.pool_name,
    (st.token0_balance_usd + st.token1_balance_usd) as total_bal,
    st.pool_address
    FROM uniswapv3.pool_stats st
    INNER JOIN (
    SELECT MAX(p1.block_timestamp) as maxDate, p1.pool_address
    FROM uniswapv3.pool_stats p1
    GROUP BY p1.pool_address
    ) p2 ON st.pool_address = p2.pool_address AND st.block_timestamp= p2.maxDate
    WHERE total_bal IS NOT NULL
    and st.pool_address != '0xcdb4e35b235845ae8d0c27f77cd8176ddfc26965'
    ORDER BY total_bal DESC
    LIMIT 10
    ),
    all_lp AS (
    SELECT
    liquidity_provider,
    pool_address,
    pool_name,
    COUNT(block_id) as n_tx
    FROM
    uniswapv3.positions
    WHERE
    pool_address in (select pool_address from top5_pools)--= '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' -- USDC-WETH v3
    GROUP BY
    liquidity_provider,pool_address,pool_name
    ORDER BY
    n_tx DESC
    ),
    Run a query to Download Data