phi-deltalyticsUniLPDailyTtl
    Updated 2023-06-08
    WITH ttl_lp_adj AS (
    SELECT
    dt,
    SUM(CASE WHEN num_mint < num_burn THEN num_mint ELSE num_burn END) AS num_adj_price_range

    FROM (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS dt,
    l.pool_address,
    l.liquidity_provider,
    COUNT(DISTINCT (CASE WHEN action = 'INCREASE_LIQUIDITY' THEN tx_hash ELSE NULL END)) AS num_mint,
    COUNT(DISTINCT (CASE WHEN action = 'DECREASE_LIQUIDITY' THEN tx_hash ELSE NULL END)) AS num_burn

    FROM
    ethereum.uniswapv3.ez_lp_actions l
    GROUP BY 1,2,3
    )
    GROUP BY dt
    )

    SELECT
    *,
    num_adj_price_range / num_lp AS avg_lp_adj
    FROM (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS dt,
    COUNT(DISTINCT liquidity_provider) AS num_lp
    FROM ethereum.uniswapv3.ez_lp_actions
    GROUP BY dt
    ) a LEFT JOIN ttl_lp_adj lp ON a.dt = lp.dt
    ORDER BY a.dt DESC



    Run a query to Download Data