lighter_robinhood.assets.perpetual_market_stats table contains perpetual market snapshots with prices, open interest, and funding.
Table Columns
Unique key:market_id, funding_timestamp
| Column Name | Data Type | Description |
|---|---|---|
market_id | NUMBER(38,0) | Market identifier. |
funding_timestamp | TIMESTAMP_NTZ(9) | Snapshot timestamp (hourly). |
symbol | VARCHAR | Market symbol (e.g., BTC, ETH). |
market_type | VARCHAR | ’perp’ or ‘spot’. |
base_symbol | VARCHAR | Base asset symbol. |
quote_symbol | VARCHAR | Quote asset symbol. |
collateral_symbol | VARCHAR | Collateral asset symbol (for example, USDG). NULL for spots. |
base_l1_address | VARCHAR | L1 contract address of the base asset, when applicable. |
index_price | FLOAT | Oracle/index price at snapshot time. |
mark_price | FLOAT | Mark price used for PnL, liquidations, and funding. |
last_trade_price | FLOAT | Last traded price at snapshot time. |
open_interest | FLOAT | Open interest in USD at snapshot time. |
open_interest_limit | NUMBER(38,0) | Maximum OI allowed. |
current_funding_rate | FLOAT | Live funding rate at snapshot time. |
funding_rate | FLOAT | Last settled funding rate. |
funding_clamp_small | FLOAT | Funding rate lower clamp parameter. |
funding_clamp_big | FLOAT | Funding rate upper clamp parameter. |
daily_base_token_volume | FLOAT | Rolling 24h volume in base units. |
daily_quote_token_volume | FLOAT | Rolling 24h volume in quote/USD. |
daily_price_low | FLOAT | Rolling 24h price low. |
daily_price_high | FLOAT | Rolling 24h price high. |
daily_price_change | FLOAT | Rolling 24h price change percentage. |
_created_at | TIMESTAMP_NTZ(9) | Timestamp when the row was first recorded. |
_updated_at | TIMESTAMP_NTZ(9) | Timestamp when the row was last updated. |
Sample Query
SELECT
funding_timestamp,
symbol,
mark_price,
open_interest,
current_funding_rate
FROM lighter_robinhood.assets.perpetual_market_stats
WHERE funding_timestamp >= CURRENT_DATE - 1
AND symbol = 'BTC'
ORDER BY funding_timestamp DESC
LIMIT 100;