tickBoundary    →  liquidityAmount
[60, 120)       →  10,910e6
...

WITH
swaps_counted AS (
  SELECT
    TIMESTAMP_TRUNC(block_timestamp, DAY) AS day_
    , COUNT(*) AS swapsCount
  FROM `lpscanner.avalanche_blackhole_swaps`
  WHERE block_timestamp > CURRENT_TIMESTAMP - INTERVAL 60 DAY
    AND pool_address = '0xa02ec3ba8d17887567672b2cdcaf525534636ea0'
  GROUP BY 1
)
, liquidity_movements_counted AS (
  SELECT
    TIMESTAMP_TRUNC(block_timestamp, DAY) AS day_
    , COUNT(*) AS liquidityMovementsCount
  FROM `lpscanner.avalanche_blackhole_liquidity_movements`
  WHERE block_timestamp > CURRENT_TIMESTAMP - INTERVAL 60 DAY
    AND pool_address = '0xa02ec3ba8d17887567672b2cdcaf525534636ea0'
  GROUP BY 1
)
SELECT
  sc.day_
  , sc.swapsCount
  , lmc.liquidityMovementsCount
FROM swaps_counted sc
LEFT JOIN liquidity_movements_counted lmc ON sc.day_ = lmc.day_

WITH
-- Positions active before the 30-day window that contribute to the initial liquidity state
active_positions_feed_before_the_term AS (
  SELECT generatedIndex, liquidityDelta, ownerAddress, salt, tickLower, tickUpper, block_timestamp
  FROM (
    SELECT
      SUM(liquidityDelta) OVER(PARTITION BY ownerAddress, salt, tickLower, tickUpper) AS cumulativeLiquidityByPosition
      , generatedIndex, liquidityDelta, ownerAddress, salt, tickLower, tickUpper, block_timestamp
    FROM lpscanner.avalanche_blackhole_liquidity_movements
    WHERE pool_address = '0xa02ec3ba8d17887567672b2cdcaf525534636ea0'
      AND generatedIndex < 80439933000022  -- limit of investigation period (block_number * 1e6 + log_index)
      AND block_timestamp < CURRENT_TIMESTAMP() - INTERVAL 29 DAY  -- partition pruning
  )
  WHERE cumulativeLiquidityByPosition > 0  -- only positions that were still active before the window
)

, the_list AS (
  SELECT * FROM (
    SELECT
      'lm' AS table_
      , generatedIndex, liquidityDelta
      , SUM(liquidityDelta) OVER(
          PARTITION BY salt, tickLower, tickUpper
          ORDER BY generatedIndex
        ) AS accumulativeL  -- cumulative liquidity for this position at this point in time
      , salt, tickLower, tickUpper, block_timestamp
    FROM (
      SELECT generatedIndex, liquidityDelta, ownerAddress, salt, tickLower, tickUpper, block_timestamp
      FROM lpscanner.avalanche_blackhole_liquidity_movements
      WHERE pool_address = '0xa02ec3ba8d17887567672b2cdcaf525534636ea0'
        AND generatedIndex >= 80439933000022
        AND block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 31 DAY
        AND liquidityDelta != 0  -- liquidityDelta = 0 means fee-collection only, skip
      UNION ALL
      SELECT * FROM active_positions_feed_before_the_term
    )

    UNION ALL

    -- Merge swaps into the unified timeline (NULL for lm-only columns)
    SELECT
      'swa' AS table_
      , generatedIndex
      , NULL AS liquidityDelta
      , NULL AS accumulativeL
      , NULL AS salt, NULL AS tickLower, NULL AS tickUpper
      , block_timestamp
    FROM lpscanner.avalanche_blackhole_swaps
    WHERE pool_address = '0xa02ec3ba8d17887567672b2cdcaf525534636ea0'
      AND generatedIndex >= 80439933000022
      AND block_timestamp > CURRENT_TIMESTAMP() - INTERVAL 31 DAY
  )
)

-- Attach the generatedIndex of the most recent swap seen so far to every event
, with_last_swap AS (
  SELECT *,
    MAX(IF(table_ = 'swa', generatedIndex, NULL))
      OVER (ORDER BY generatedIndex ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS last_swap_gi
  FROM the_list
)

, lm_events AS (
  SELECT *,
    LAG(accumulativeL, 1, 0)
      OVER (PARTITION BY salt, tickLower, tickUpper ORDER BY generatedIndex)
    AS prev_accumulativeL  -- previous accumulated liquidity for this position
  FROM with_last_swap
  WHERE table_ = 'lm'
)

-- Carry forward the generatedIndex of the most recent open event per position.
-- An "open" is: prev_accumulativeL = 0 AND accumulativeL > 0.
, lm_with_cycle AS (
  SELECT *,
    LAST_VALUE(
      IF(prev_accumulativeL = 0 AND accumulativeL > 0, generatedIndex, NULL)
      IGNORE NULLS
    ) OVER (
      PARTITION BY salt, tickLower, tickUpper
      ORDER BY generatedIndex
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cycle_open_gi
  FROM lm_events
)

-- A cycle is unutilized if it closed before any swap occurred after its open
, unutilized_cycles AS (
  SELECT DISTINCT salt, tickLower, tickUpper, cycle_open_gi
  FROM lm_with_cycle
  WHERE accumulativeL = 0
    AND cycle_open_gi IS NOT NULL
    AND cycle_open_gi >= 80439933000022
    AND COALESCE(last_swap_gi, 0) < cycle_open_gi
)

-- Label every lm event — 'unutilized' if its cycle matches, 'utilized' otherwise
, lm_labeled AS (
  SELECT l.*,
    IF(u.cycle_open_gi IS NOT NULL, 'unutilized', 'utilized') AS utilization_label
  FROM lm_with_cycle l
  LEFT JOIN unutilized_cycles u USING (salt, tickLower, tickUpper, cycle_open_gi)
)

-- Final output: labeled lm events merged back with swap events
SELECT
  table_, generatedIndex, liquidityDelta, accumulativeL,
  salt, tickLower, tickUpper, block_timestamp,
  utilization_label, last_swap_gi
FROM lm_labeled
UNION ALL
SELECT
  table_, generatedIndex, NULL, NULL,
  NULL, NULL, NULL, block_timestamp,
  NULL AS utilization_label, last_swap_gi
FROM with_last_swap
WHERE table_ = 'swa'
ORDER BY generatedIndex

SELECT
  *
  , SUM(total_gas_cost_avax) OVER (ORDER BY day_)
FROM (
  SELECT
    DATE_TRUNC('day', block_date)                              AS day_
    , "from"
    , COUNT(*)                                                 AS transaction_count
    , SUM(CAST(gas_price * gas_used AS DECIMAL)) / POW(10, 18) AS total_gas_cost_avax
  FROM avalanche_c.transactions
  WHERE "to" = 0x07880d914bad719175099a362a88606eb975ffdc
  GROUP BY 1, 2
)