Uniswap V3 Pool State Table

Description

Time-series snapshots of V3 pool state with active liquidity, current tick, sqrt price (x96), and per-token balances/TVL.

Schema

Field	Type	Description
id	BYTES	Deterministic ID (MD5 of pool_id, block_number)
uniswap_v3_pool_id	STRING	FK → uniswap_v3_pool.id.
block_number	INTEGER	Snapshot block.
datetime	TIMESTAMP	Snapshot time (UTC).
raw_amount_0	BIGNUMERIC	Token0 balance (smallest unit).
raw_amount_1	BIGNUMERIC	Token1 balance (smallest unit).
value_usd_0	FLOAT	USD value of token0 at snapshot.
value_usd_1	FLOAT	USD value of token1 at snapshot.
current_tick	INTEGER	Current tick.
sqrt_price_x96	BIGNUMERIC	Sqrt price * 2^96.
active_liquidity	BIGNUMERIC	Active liquidity at current tick.
ingested_at	TIMESTAMP	Insert time (UTC).

Example Queries

Latest TVL snapshot per pool (token breakdown)

SELECT
  p.name,
  (SELECT MAX(datetime) FROM `defi_prepared.uniswap_v3_pool_state`) AS as_of,
  s.value_usd_0 + s.value_usd_1 AS tvl_usd,
  s.value_usd_0 AS token0_usd,
  s.value_usd_1 AS token1_usd
FROM `defi_prepared.uniswap_v3_pool_state` s
JOIN `defi_prepared.uniswap_v3_pool` p ON s.uniswap_v3_pool_id = p.id
QUALIFY ROW_NUMBER() OVER (PARTITION BY s.uniswap_v3_pool_id ORDER BY s.datetime DESC) = 1
ORDER BY tvl_usd DESC
LIMIT 50;

Liquidity concentration (active / total) for a given date

WITH latest AS (
  SELECT s.*, ROW_NUMBER() OVER (PARTITION BY s.uniswap_v3_pool_id ORDER BY s.datetime DESC) rn
  FROM `defi_prepared.uniswap_v3_pool_state` s
  WHERE DATE(s.datetime) = DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
)
SELECT p.name, SAFE_DIVIDE(active_liquidity, NULLIF(active_liquidity,0) + 0) AS active_share
FROM latest
JOIN `defi_prepared.uniswap_v3_pool` p ON p.id = latest.uniswap_v3_pool_id
WHERE rn = 1
ORDER BY active_share DESC;