sibutasinesipo2024-04-13 02:22 PM
    Updated 2024-04-14
    WITH PositionEvents AS (
    SELECT
    POOL_NAME,
    BLOCK_TIMESTAMP,
    CASE
    WHEN IS_ACTIVE = 'TRUE' THEN 'Opened'
    WHEN IS_ACTIVE = 'FALSE' THEN 'Closed'
    ELSE 'Unknown'
    END AS PositionStatus,
    LIQUIDITY_ADJUSTED,
    CASE
    WHEN PRICE_LOWER_0_1_USD = 0 OR PRICE_UPPER_0_1_USD = 0 THEN NULL
    ELSE (PRICE_UPPER_0_1_USD - PRICE_LOWER_0_1_USD) / NULLIF(PRICE_LOWER_0_1_USD, 0)
    END AS PriceRange
    FROM
    ethereum.uniswapv3.ez_positions
    ),

    VolatilityAnalysis AS (
    SELECT
    POOL_NAME,
    BLOCK_TIMESTAMP,
    PositionStatus,
    COUNT(*) OVER (PARTITION BY POOL_NAME, PositionStatus ORDER BY BLOCK_TIMESTAMP) AS CumulativeCount,
    AVG(LIQUIDITY_ADJUSTED) OVER (PARTITION BY POOL_NAME ORDER BY BLOCK_TIMESTAMP) AS AvgLiquidity,
    AVG(PriceRange) OVER (PARTITION BY POOL_NAME ORDER BY BLOCK_TIMESTAMP) AS AvgPriceRange
    FROM
    PositionEvents
    )

    SELECT *
    FROM
    VolatilityAnalysis
    ORDER BY
    POOL_NAME,
    BLOCK_TIMESTAMP;

    QueryRunArchived: QueryRun has been archived