Uniswap v4 Position Holdings Hourly

-- Parameters to alter:
-- id: identifier of v4 pool: ('0x77ea9d2be50eb3e82b62db928a1bcc573064dd2a14f5026847e755518c8659c9')
-- time_granularity: time granularity of address: (activity_hour, day, week, etc)
-- start_period: start of date range of interest ('2025-06-01')
-- end_period: end of date range of interest (CURRENT_DATE)

WITH
  distinct_pool_positions AS (
    SELECT
      id,
      position,
      mint_block_timestamp
    FROM
      unichain.dex.uniswap_v4_positions
    WHERE
      id = '{{id}}'
  ),
  distinct_dates AS (
    SELECT
      DATE_TRUNC('{{time_granularity}}', timestamp) AS date,
      COUNT(*) AS block_count
    FROM
      unichain.raw.blocks
    WHERE
      timestamp >= '{{start_period}}'
      and timestamp <= {{end_period}}
    GROUP BY
      date
  ),
  date_position_cte AS (
    SELECT
      dd.date,
      dpp.id,
      dpp.position,
      dpp.mint_block_timestamp
    FROM
      distinct_dates dd
      CROSS JOIN distinct_pool_positions dpp
    WHERE
      dpp.mint_block_timestamp <= dd.date -- only include timestamps after the position was minted
  ),
  position_holdings AS (
    SELECT
      *
    FROM
      unichain.dex.uniswap_v4_position_holdings_hourly
    WHERE
      id = '{{id}}'
      and activity_hour <= {{end_period}}
  ),
  final AS (
    SELECT
      dp.date,
      ph.*
    FROM
      date_position_cte dp ASOF
      JOIN position_holdings ph MATCH_CONDITION (dp.date >= ph.activity_hour) ON dp.position = ph.position
      AND dp.id = ph.id
  )
SELECT
  date,
  id,
  SUM(tvl_usd) AS total_tvl_usd,
  COUNT(*) AS record_count,
  SUM(token0_amount) AS total_token0,
  SUM(token1_amount) AS total_token1
FROM
  final
GROUP BY
  all
ORDER BY
  date desc