CryptoLionfees over LPs
    Updated 2023-03-27
    SELECT
    date_trunc('day',block_timestamp) as day,
    pool_address,
    pool_name,
    count(distinct liquidity_provider) as lps,
    sum(amount0_adjusted + amount1_adjusted)/lps as fees_collected_per_provider
    FROM uniswapv3.position_collected_fees
    WHERE block_timestamp >= getdate() - interval '30 days'
    AND pool_name LIKE 'USDC-USDT%' -- usdc-usdt
    OR pool_name LIKE 'USDC-WETH%' -- usdc-weth
    OR pool_name LIKE 'WETH-USDC%' -- weth-usdc
    OR pool_name LIKE 'UNI-WETH%' -- uni-weth
    -- OR pool_name LIKE 'DOKE-USDT%' -- doke-usdt
    OR pool_name LIKE 'DAI-USDC%' -- dai-usdc
    OR pool_name LIKE 'FEI-USDC%' -- fei-usdc
    OR pool_name LIKE 'DAI-WETH%' -- dai-weth
    OR pool_name LIKE 'SHIBA2-WETH%' -- shiba2-weth
    GROUP BY day, pool_address, pool_name
    HAVING fees_collected_per_provider > 0
    ORDER BY DAY