Overview
- Dataset ID:
0412 - Table:
blockdb_evm.b0412_liquidity_pools_yields_ranges_v1 - What it is: Range-aware yield/ROI predictions per pool over fixed horizons (1D, 3D, 7D, 14D, 30D, 90D, 365D), computed for positions at different price ranges around the current mid price. Designed for concentrated liquidity AMMs (Uniswap v3/v4, etc.).
- Grain: 1 row per (pool, as-of event triple, horizon, range_bps).
- Primary location fields:
pool_uid,block_number,block_time(UTC),tx_index,log_index - Join keys:
- Pool:
pool_uid→blockdb_evm.b0211_liquidity_pools_v1.pool_uid - Provenance:
(block_number, tx_index, log_index)→blockdb_evm.b0103_logs_v1
- Pool:
- API: POST /evm/yields/ranges
This dataset emits aligned arrays (one element per pool token). Use
tokens[] to line up current_reserves[], position_amounts[], volumes, fees, and ROI predictions.Core concepts
- Range parameters
range_bps: Standard range in basis points (0=full-range, 100=±1%, 500=±5%, 1000=±10%, 2000=±20%)lower_tick/upper_tick: Actual tick bounds for the range (NULL for full-range)
- Target horizon vs observed history
target_period_days: horizon you requested (1, 3, 7, 14, 30, 90, 365)observed_period_days: how many days of history were available/used
- Extrapolation
- When
observed_period_days < target_period_days, the dataset marks the row as extrapolated and scales observed values by:- (extrapolation_factor = target_period_days / observed_period_days)
- When
- ROI
- For each token index
i:- (roi_predicted[i] = user_fees_predicted[i] / position_amounts[i])
- For each token index
Columns
| Column | Type | Description |
|---|---|---|
id | BIGINT | Surrogate identity for the yield record (auto-incremented). |
pool_uid | BYTEA | Pool identifier (internal). |
exchange_id | INTEGER | Exchange/DEX identifier. |
type_id | INTEGER | Pool type identifier (FK to liquidity_pool_types). |
block_number | BIGINT | Block height of the as-of snapshot anchor. |
block_time | TIMESTAMPTZ | UTC timestamp of the as-of snapshot anchor. |
tx_index | INTEGER | Transaction index within the block. |
log_index | INTEGER | Log index within the transaction. |
target_period_days | SMALLINT | Target horizon in days. Allowed: 1, 3, 7, 14, 30, 90, 365. |
observed_period_days | SMALLINT | History actually used (must be > 0 and <= target_period_days). |
is_full_period | BOOLEAN | true when observed_period_days == target_period_days. |
is_extrapolated | BOOLEAN | true when the row is scaled from shorter history. |
extrapolation_factor | NUMERIC(18,6) | Scaling factor applied when extrapolating (must be > 1 when extrapolated; 1 when full period). |
window_start_time | TIMESTAMPTZ | Start time of the observed window. |
window_end_time | TIMESTAMPTZ | End time of the observed window. |
range_bps | INTEGER | Standard range in bps (0=full-range, 100=±1%, 500=±5%, 1000=±10%, 2000=±20%). |
lower_tick | INTEGER | Lower tick bound for the range (NULL for full-range). |
upper_tick | INTEGER | Upper tick bound for the range (NULL for full-range). |
tokens | BYTEA[] | Pool token addresses (aligned arrays index by this order). |
current_reserves | NUMERIC(78,18)[] | Current reserves per token (decimals-adjusted). |
position_amounts | NUMERIC(78,18)[] | Position token amounts assumed for ROI denominator (aligned to tokens). |
volume_observed | NUMERIC(78,18)[] | Observed traded volume per token in the window. |
volume_predicted | NUMERIC(78,18)[] | Predicted traded volume per token for the target horizon. |
user_fees_observed | NUMERIC(78,18)[] | Observed user/LP fees per token in the window. |
user_fees_predicted | NUMERIC(78,18)[] | Predicted user/LP fees per token for the target horizon. |
roi_predicted | NUMERIC(78,18)[] | Predicted ROI fraction per token for the target horizon. |
_tracing_id | BYTEA | Deterministic BlockDB lineage identifier for the yield record. |
_parent_tracing_ids | BYTEA[] | Tracing IDs for upstream derived records referenced during computation. |
_created_at | TIMESTAMPTZ | Record creation timestamp. |
_updated_at | TIMESTAMPTZ | Record update timestamp. |
Use Cases
- Concentrated liquidity position optimization (selecting optimal range width)
- Range selection analysis for Uniswap v3/v4 positions
- Yield comparison across different range widths (narrow vs. wide)
- Impermanent loss vs. fee revenue trade-off analysis
- LP strategy backtesting for CLAMM pools