CryptoLionTop Providers
    Updated 2022-11-27
    WITH x as (SELECT
    lpa.liquidity_provider,
    SUM(CASE WHEN action LIKE 'INC%' THEN lpa.amount0_usd+lpa.amount1_usd ELSE 0 END) as mint,
    SUM(CASE WHEN action LIKE 'DEC%' THEN lpa.amount0_usd+lpa.amount1_usd ELSE 0 END) as burn,
    SUM(pcf.amount0_usd+pcf.amount1_usd) as collected_fees,
    mint-burn as net_liquidity,
    count(distinct lpa.pool_name) as pools
    FROM uniswapv3.lp_actions lpa
    INNER JOIN uniswapv3.position_collected_fees pcf on pcf.liquidity_provider = lpa.liquidity_provider
    GROUP BY lpa.liquidity_provider
    HAVING net_liquidity > 0
    ORDER BY net_liquidity DESC),
    y as (
    SELECT
    liquidity_provider,
    sum(fee_usd) as gas_fees
    FROM uniswapv3.lp_actions l
    INNER JOIN ethereum.transactions t on t.tx_id = l.tx_id
    GROUP BY liquidity_provider
    ORDER BY gas_fees
    )

    SELECT
    x.liquidity_provider,
    ROUND(net_liquidity,2) as liquidity_provided_usd,
    ROUND(collected_fees,2) as collected_fees,
    ROUND(gas_fees,2) as gas_fees,
    pools
    FROM x
    INNER JOIN y on x.liquidity_provider = y.liquidity_provider
    HAVING liquidity_provided_usd > 0
    ORDER BY liquidity_provided_usd DESC