Pool LP Position

Overview

The pool_lp_position table tracks individual liquidity provider positions over time, enabling analysis of LP behavior and concentration. Each row represents a single token position for an LP at a specific time.

Schema

Field	Type	Mode	Description
id	BYTES	REQUIRED	Client-generated deterministic ID using MD5 hash of (liquidity_pool_id, token_contract_id, owner, datetime). 128-bit hash stored as BYTES
liquidity_pool_id	STRING	REQUIRED	Foreign key to liquidity_pool table
token_contract_id	STRING	REQUIRED	Foreign key to token_contract table
block_number	INTEGER	NULLABLE	Block number when this position was recorded
datetime	TIMESTAMP	REQUIRED	UTC timestamp for this position snapshot
owner	STRING	REQUIRED	Wallet address of the liquidity provider
raw_amount	BIGNUMERIC	REQUIRED	Token amount held by LP (in smallest unit) - uses BIGNUMERIC to handle all token decimals without overflow
value_usd	FLOAT	NULLABLE	USD value of the position at measurement time
ingested_at	TIMESTAMP	REQUIRED	Timestamp when this record was inserted into BigQuery

Primary Key

• id: A deterministic ID generated using MD5 hash of liquidity_pool_id, token_contract_id, owner, and datetime

Foreign Keys

• liquidity_pool_id: References the liquidity_pool table
• token_contract_id: References the token_contract table

Usage Examples

-- Find top LPs by total value locked across all pools
SELECT 
  plp.owner,
  SUM(plp.value_usd) AS total_value_locked_usd,
  COUNT(DISTINCT plp.liquidity_pool_id) AS pool_count
FROM `bitpulse.dex.pool_lp_position` plp
WHERE plp.datetime = (
  SELECT MAX(datetime) 
  FROM `bitpulse.dex.pool_lp_position`
)
GROUP BY plp.owner
ORDER BY total_value_locked_usd DESC
LIMIT 100;

-- Track a specific LP's positions over time
SELECT 
  plp.datetime,
  lp.name AS pool_name,
  tc.symbol,
  plp.raw_amount / POWER(10, tc.decimals) AS token_amount,
  plp.value_usd
FROM `bitpulse.dex.pool_lp_position` plp
JOIN `bitpulse.dex.liquidity_pool` lp ON plp.liquidity_pool_id = lp.id
JOIN `bitpulse.dex.token_contract` tc ON plp.token_contract_id = tc.id
WHERE plp.owner = '0x1234567890abcdef1234567890abcdef12345678'
ORDER BY plp.datetime DESC, lp.name, tc.symbol;

Notes

• LP position snapshots are typically taken at regular intervals (e.g., daily).
• The raw_amount field stores token amounts in their smallest unit (e.g., wei for ETH) and should be divided by 10^decimals to get the human-readable amount.
• The value_usd field may be NULL if price data was not available at the time of ingestion.
• This table can be used to analyze LP concentration, behavior patterns, and position changes over time.
• For concentrated liquidity protocols like Uniswap V3, this table captures the actual token amounts in the position, not the full range.