hyperliquid.assets.perpetual_positions_daily view contains daily Hyperliquid perpetual position state per (user, type, coin). Days where the position is known to be closed are flagged via is_tombstone = true.
Table Details
| Property | Value |
|---|---|
| Table Name | hyperliquid.assets.perpetual_positions_daily |
| Table Status | Production-Ready |
| Unique Key | time_slice, user, type, coin |
To filter to currently-open positions use
WHERE NOT is_tombstone. Use last_activity_timestamp rather than time_slice for freshness.Table Columns
Identifiers
| Column Name | Data Type | Description |
|---|---|---|
| time_slice | TIMESTAMP_NTZ(9) | Calendar day for this row. |
| user | VARCHAR(16777216) | User wallet address. |
| type | VARCHAR(16777216) | Position type. Currently ‘oneWay’. |
| coin | VARCHAR(16777216) | Coin of the position. |
| timestamp | TIMESTAMP_NTZ(9) | End-of-day timestamp (midnight TIMESTAMP_NTZ). |
| szi | VARCHAR(16777216) | Size of the position. ‘0’ on tombstone rows. |
| leverage_type | VARCHAR(16777216) | Type of leverage. NULL on tombstone rows. |
| leverage_value | NUMBER(38,0) | Leverage multiplier. NULL on tombstone rows. |
| entry_px | VARCHAR(16777216) | Entry price. NULL on tombstone rows. |
| position_value | VARCHAR(16777216) | Position value. Zeroed on tombstone rows. |
| unrealized_pnl | VARCHAR(16777216) | Unrealized PnL. Zeroed on tombstone rows. |
| return_on_equity | VARCHAR(16777216) | Return on equity. Zeroed on tombstone rows. |
| liquidation_price | VARCHAR(16777216) | Price that would trigger liquidation. NULL on tombstone rows. |
| margin_used | VARCHAR(16777216) | Margin used. Zeroed on tombstone rows. |
| max_leverage | NUMBER(38,0) | Maximum leverage allowed. NULL on tombstone rows. |
| cumulative_funding_all_time | VARCHAR(16777216) | Cumulative funding paid / received all time. NULL on tombstone rows. |
| cumulative_funding_since_open | VARCHAR(16777216) | Cumulative funding paid / received since the position was opened. Zeroed on tombstone rows. |
| cumulative_funding_since_change | VARCHAR(16777216) | Cumulative funding paid / received since the last position change. Zeroed on tombstone rows. |
| last_activity_timestamp | TIMESTAMP_NTZ(9) | Sub-second timestamp of the most recent observed activity for this (user, type, coin). |
| is_tombstone | BOOLEAN | True when the position for this day is known to be closed. Use WHERE NOT is_tombstone to filter to open positions. |
| closure_ts | TIMESTAMP_NTZ(9) | For tombstone rows, the earliest fill timestamp at which the position reached zero. NULL otherwise. |
| _created_at | TIMESTAMP_NTZ(9) | Row creation timestamp. |
| _updated_at | TIMESTAMP_NTZ(9) | Row last update timestamp. |
Position
| Column Name | Description | |
|---|---|---|
| szi | Size of the position. ‘0’ on tombstone rows. | The size of the position. |
| leverage_type | Type of leverage. NULL on tombstone rows. | The type of leverage used for this position. |
| leverage_value | Leverage multiplier. NULL on tombstone rows. | The leverage multiplier used for this position. |
| entry_px | Entry price. NULL on tombstone rows. | The entry price of the position. |
| position_value | Position value. Zeroed on tombstone rows. | The position value. |
| unrealized_pnl | Unrealized PnL. Zeroed on tombstone rows. | The unrealized PnL of the position. |
| return_on_equity | Return on equity. Zeroed on tombstone rows. | The return on equity of the position. |
| liquidation_price | Price that would trigger liquidation. NULL on tombstone rows. | The price which will trigger a liquidation of the position. |
| margin_used | Margin used. Zeroed on tombstone rows. | The margin used to maintain the position. |
| max_leverage | Maximum leverage allowed. NULL on tombstone rows. | Maximum leverage allowed for this position at the snapshot. Usually matches the market cap at that time. The user’s selected leverage is leverage_value. |
Funding
| Column Name | Description | |
|---|---|---|
| cumulative_funding_all_time | Cumulative funding paid / received all time. NULL on tombstone rows. | The cumulative funding paid/received all time. |
| cumulative_funding_since_open | Cumulative funding paid / received since the position was opened. Zeroed on tombstone rows. | The cumulative funding paid/received since the position was opened. |
| cumulative_funding_since_change | Cumulative funding paid / received since the last position change. Zeroed on tombstone rows. | The cumulative funding paid/received since the last position change. |
Sample Query
SELECT
time_slice,
user,
coin,
szi,
position_value,
unrealized_pnl
FROM hyperliquid.assets.perpetual_positions_daily
WHERE time_slice >= CURRENT_DATE - 7
AND coin = 'BTC'
AND NOT is_tombstone
ORDER BY time_slice DESC, ABS(position_value::float) DESC
LIMIT 100