Pool OHLCV Table

Description

OHLCV time series per pool & direction (base/quote). Includes USD OHLCV as well as raw price ratios and quote-denominated volume. Useful for charts, volatility, and cross-venue comparisons.

Schema

Field	Type	Description
id	BYTES	Deterministic ID (MD5 of key fields).
liquidity_pool_id	STRING	FK to the generic pool registry.
base_token_contract_id	STRING	FK → token_contract.id (base).
quote_token_contract_id	STRING	FK → token_contract.id (quote).
block_number	INTEGER	Block at end of the candle (nullable).
datetime	TIMESTAMP	Candle timestamp (UTC).
open_usd	FLOAT	Opening price in USD.
high_usd	FLOAT	High in USD.
low_usd	FLOAT	Low in USD.
close_usd	FLOAT	Close in USD.
volume_usd	FLOAT	Total traded volume (USD).
open	FLOAT	Opening quote/base price ratio.
high	FLOAT	High quote/base ratio.
low	FLOAT	Low quote/base ratio.
close	FLOAT	Close quote/base ratio.
quote_volume	FLOAT	Volume in quote token units.
ingested_at	TIMESTAMP	Insert time (UTC).

Example Queries

30-day top pools by USD volume (cross-DEX)

SELECT  SUM(o.volume_usd) AS volume_30d,
        DATE_TRUNC(o.datetime, DAY) AS day
FROM `defi_prepared.pool_ohlcv` o
WHERE o.datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY day
ORDER BY day DESC;

Daily price & volume for WETH quoted in USDC across venues

SELECT
  DATE(o.datetime) AS date,
  AVG(o.close_usd) AS avg_close_usd,
  SUM(o.volume_usd) AS daily_volume_usd
FROM `defi_prepared.pool_ohlcv` o
JOIN `defi_prepared.token_contract` b ON o.base_token_contract_id = b.id
JOIN `defi_prepared.token_contract` q ON o.quote_token_contract_id = q.id
WHERE b.symbol = 'WETH' AND q.symbol = 'USDC'
  AND o.datetime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date DESC;