Uniswap V3 Position Table

Description

NFT-based liquidity positions with range and valuation per snapshot. Ideal for LP strategy analysis and "in-range" detection.

Schema

Field	Type	Description
id	BYTES	Deterministic ID (MD5 of pool_id, position_id, block_number)
uniswap_v3_pool_id	STRING	FK → uniswap_v3_pool.id.
datetime	TIMESTAMP	Snapshot time (UTC).
block_number	INTEGER	Snapshot block.
position_id	INTEGER	Position NFT ID.
owner_address	STRING	Current owner address.
raw_amount_0	BIGNUMERIC	Token0 amount (smallest unit).
raw_amount_1	BIGNUMERIC	Token1 amount (smallest unit).
value_usd_0	FLOAT	USD value of token0 in the position.
value_usd_1	FLOAT	USD value of token1 in the position.
liquidity	BIGNUMERIC	Amount of liquidity provided.
lower_tick	INTEGER	Lower bound of the range.
upper_tick	INTEGER	Upper bound of the range.
active_range	BOOLEAN	TRUE if current price is within the range.
ingested_at	TIMESTAMP	Insert time (UTC).

Example Queries

Latest snapshot of LPs in a specific pool

DECLARE target_pool STRING DEFAULT 'WETH/USDC 0.05%';

WITH target AS (
  SELECT id FROM `defi_prepared.uniswap_v3_pool` WHERE name = target_pool LIMIT 1
)
SELECT pos.*
FROM `defi_prepared.uniswap_v3_position` pos
WHERE pos.uniswap_v3_pool_id = (SELECT id FROM target)
QUALIFY ROW_NUMBER() OVER (PARTITION BY position_id ORDER BY datetime DESC) = 1
ORDER BY position_id;

All "in-range" positions at a specific timestamp

SELECT p.name, pos.position_id, pos.owner_address, pos.liquidity
FROM `defi_prepared.uniswap_v3_position` pos
JOIN `defi_prepared.uniswap_v3_pool` p ON pos.uniswap_v3_pool_id = p.id
WHERE pos.datetime = TIMESTAMP('2025-01-01 00:00:00+00')
  AND pos.active_range = TRUE
ORDER BY p.name, pos.position_id;