Uniswap v4 Position Holdings Hourly

The dex.uniswap_v4_position_holdings_hourly table tracks the hourly changes in v4 position holdings. Changes are derived from position-level liquidity and pool-level price changes. This enables accurate tracking of position and pool level LP holdings, amounts, and TVL over time.

Status: This model is currently in beta and may be subject to change.

Note: This table only captures hourly changes to Uniswap v4 position amounts. To obtain the full state at a given hour/day (e.g., for TVL or amount0/1 snapshots), a forward-fill of historical records is required. See example query below.

Methodology

This table captures updates from both position-level and pool-level changes:
- Position-level changes come from the ModifyLiquidity v4 event. These affect individual positions and require cumulative sums of liquidity up to each hour. Contextual details are stored in extra_position_fields.
- Pool-level changes are derived from the most recent Swap or Initialize v4 event in each hour. These impact all positions within the pool. Contextual swap activity is stored in extra_pool_fields and static fields are stored in static_pool_fields.
Positions are uniquely identified by a synthetic ID built from (id, tickLower, tickUpper) due to the absence of an emitted token_id in v4. The final model performs an outer join between the liquidity and price change updates at the (pool, position, hour) level. It forward-fills missing data and computes token amounts using Uniswap source math (inputs: sqrt_price_x96, tick, tick_lower, tick_upper, liquidity).

Change Types

Change Type	Source Event(s)	Impact	Granularity	Description
Pool-level change	`Swap`, `Initialize`	All positions in pool	Hourly	Captures latest tick and sqrtPriceX96 for the pool in a given hour
Position-level change	`ModifyLiquidity`	Specific position	Hourly (cumulative)	Tracks liquidity deltas per position; cumulative sum required per hour

TVL in USD is defined as the sum of amount0_usd and amount1_usd across all positions in a given pool, priced at the latest hourly pool tick.

Sample Query

How to generate daily balances from block-level balances table.

Generating Daily TVL of v4 Pool from Hourly Changes

-- 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

Table Columns

Unique Key: position, activity_hour

Column Name	Data Type	Description
id	VARCHAR	Unique pool identifier used for grouping related positions
position	VARCHAR	Synthetic identifier for a position (id + tickLower + tickUpper)
activity_hour	TIMESTAMP_NTZ(9)	Hourly timestamp for the observation
sqrt_price_x96	VARCHAR	Square root price of the pool in Q64.96 format
sqrt_price	VARCHAR	Human-readable square root price
is_in_range	BOOLEAN	Whether the position is active (in-range) at this hour
tick	NUMBER(38,0)	Current pool tick
tick_upper	NUMBER(38,0)	Upper tick boundary of the position
tick_lower	NUMBER(38,0)	Lower tick boundary of the position
liquidity	NUMBER(38,0)	Current liquidity provided by the position
token0_address	VARCHAR	Token0 contract address
token0_name	VARCHAR	Token0 name
token0_symbol	VARCHAR	Token0 symbol
amount0_raw	VARCHAR	Token0 raw amount (unadjusted for decimals)
token0_amount	FLOAT	Token0 amount adjusted for decimals
token0_usd_amount	FLOAT	USD value of token0 at the activity hour
token1_address	VARCHAR	Token1 contract address
token1_name	VARCHAR	Token1 name
token1_symbol	VARCHAR	Token1 symbol
amount1_raw	VARCHAR	Token1 raw amount (unadjusted for decimals)
token1_amount	FLOAT	Token1 amount adjusted for decimals
token1_usd_amount	FLOAT	USD value of token1 at the activity hour
tvl_usd	FLOAT	Total USD value of the position (token0 + token1) at current tick
static_pool_fields	VARIANT	Static metadata about the pool (e.g., tick spacing, fee tier)
extra_pool_fields	VARIANT	Hourly pool context (e.g., swaps, block metadata)
extra_position_fields	VARIANT	Hourly position context (e.g., liquidity change summary, block metadata)
_updated_at	TIMESTAMP_NTZ(9)	Timestamp of last update to the row
_created_at	TIMESTAMP_NTZ(9)	Timestamp when the row was created

Overview

Data Verticals

Solana

Hyperliquid

Lighter

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Methodology

Change Types

Sample Query

Table Columns

​Methodology

​Change Types

​Sample Query

​Table Columns

Methodology

Change Types

Sample Query

Table Columns