Uniswap v4 Position Holdings Hourly

The dex.uniswap_v4_position_holdings_hourly table contains the position holdings of Uniswap v4 positions at the hourly granularity. It reflects the cumulative liquidity state of each position per hour based on liquidity and price changes, enabling accurate tracking of 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 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: (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 hour <= {{end_period}}
  ),
  final AS (
    SELECT
      dp.date,
      ph.*
    FROM
      date_position_cte dp ASOF
      JOIN position_holdings ph MATCH_CONDITION (dp.date >= ph.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, 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)
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 given 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 given 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 🌱

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

Methodology

Change Types

Sample Query

Table Columns

Overview

Data Verticals

Solana

Hyperliquid 🌱

Bitcoin Ecosystem

EVM

Cosmos Ecosystem

Move Ecosystem

Other Ecosystems

​Methodology

​Change Types

​Sample Query

​Table Columns

Methodology

Change Types

Sample Query

Table Columns