> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.

# Uniswap v4 Position Holdings Hourly

> Get position holdings of Uniswap v4 positions at the hourly granularity and power pool-level TVL calculations.

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.

<Info>
  * 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.
</Info>

### 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](https://blog.uniswap.org/uniswap-v3-math-primer) (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 |

<Info>
  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.
</Info>

## Sample Query

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

<Accordion title="Generating Daily TVL of v4 Pool from Hourly Changes">
  ```sql theme={null}
  -- 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
  ```
</Accordion>

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