hyperliquid.perp_positions_hourly

hyperliquid.perp_positions_hourly is the per-account position surface for Hyperliquid perpetual futures. Grain: one row per (trader, coin, block_hour), taken at the single global funding round that clears every market and account each hour. It covers first-party markets (coin unprefixed, e.g. BTC) and HIP-3 builder-deployed markets (coin = dex:SYMBOL, e.g. xyz:TSLA); spot and HIP-4 outcome markets are out of scope.

Table schema

Column	Type	Description
`block_month`	`DATE`	First partition key. Filter it to bound a read in time; only this column prunes partitions outright
`block_date`	`DATE`	Day of `block_hour`. Not a partition key, but with one file per month partition its min/max still skips whole months
`block_hour`	`TIMESTAMP(3) WITH TIME ZONE`	The funding round the position was open at, truncated to the hour
`block_number`	`BIGINT`	Block of the funding round. One value per round, so it identifies the round rather than the row
`perp_dex`	`VARCHAR`	`hyperliquid` for first-party markets, otherwise the builder DEX code (`xyz`, `flx`, `para`, …)
`coin`	`VARCHAR`	Venue-native market id. The join key to `hyperliquid.perp_market_details`
`market_symbol`	`VARCHAR`	`coin` with the `dex:` prefix stripped. Not unique across dexes, so never join on it
`asset_id`	`BIGINT`	Numeric asset id used by the raw action tables. Null for a market not yet in the poller’s registry
`trader`	`VARBINARY`	Account holding the position. Vaults, HLP and its child vaults included, are ordinary unflagged accounts here
`trader_prefix`	`VARCHAR`	Second partition key: the first byte of `trader` as two lowercase hex characters, so an address starting `0xab` lands in `ab`. Filter it alongside `trader` or nothing prunes
`position_size`	`DOUBLE`	Signed size in base units; negative is short, and never zero
`position_side`	`VARCHAR`	`long` or `short`, from the sign of `position_size`
`valuation_price`	`DOUBLE`	The market’s close price carried forward, taken from `perp_market_metrics_hourly`
`position_notional_usd`	`DOUBLE`	`abs(position_size) * valuation_price`
`funding_amount_usd`	`DOUBLE`	Funding settled on this position in this round, signed from the account’s perspective: negative means it paid
`funding_rate`	`DOUBLE`	The round’s settled rate for the market, identical across every account in the round
`_updated_at`	`TIMESTAMP(3) WITH TIME ZONE`	Build timestamp. Rows are immutable once the round is ingested

A snapshot series, not a position history

Rows exist only for positions open at the top of the hour. A close is the absence of a row in the next hour, never a zero-size row, and a position opened and closed inside the same hour never appears at all. Do not read consecutive rows as events: to detect a close, compare the set of (trader, coin) keys between two hours rather than looking for a terminating row. History starts 2025-09-27, the floor of the funding feed. hyperliquid.perp_trades reaches further back (2025-07-27), so a position held in the trades era has no snapshot before that date.

The newest hour is missing rather than empty: the funding round takes 30-75+ minutes to clear ingestion. Filter block_hour < date_trunc('hour', now()) for complete data, and never read a zero count at the current hour as zero open interest.

A second gap comes from pricing, and it can reach further back than the funding lag. valuation_price is joined from perp_market_metrics_hourly with an inner join, and the funding feed runs ahead of that build, so an hour whose price is not built yet withholds its position rows entirely instead of emitting them with a null notional. The next run merges them in.

valuation_price and open interest

valuation_price is open_interest_usd / open_interest_units from perp_market_metrics_hourly — the market’s last close at or before the hour, carried forward. Both tables therefore price positions with one rule, and rolling this table up to (coin, block_hour) reproduces that table’s open interest exactly:

SELECT
  block_hour,
  coin,
  SUM(position_notional_usd) AS open_interest_usd,
  SUM(ABS(position_size))    AS open_interest_units,
  COUNT(*)                   AS open_positions
FROM hyperliquid.perp_positions_hourly
WHERE block_month >= DATE_TRUNC('month', current_date - INTERVAL '7' day)
  AND block_date >= current_date - INTERVAL '7' day
  AND block_date < current_date
GROUP BY 1, 2

That sum counts both sides — every long is matched by a short — which is the convention the Hyperliquid UI and API use. Classical one-sided open interest is half of it. Because it is a trade price rather than a mark price, a position’s notional differs from the Hyperliquid UI’s mark-priced value by the trade-to-mark basis. For mark and oracle prices at minute cadence, use hyperliquid.perp_oracle_prices.

Funding

funding_amount_usd is the payment the venue actually settled on that position in that round, not a rate applied to a notional you compute yourself — use it directly for per-account funding cost. A long pays (negative) when funding_rate is positive and receives when it is negative; a short is the mirror. funding_amount_usd = 0 means the payment rounded to zero on a live position, not a disabled market. funding_rate repeats on every row of a (coin, block_hour), so average it per market-hour rather than per position. It is the rate as settled, not the forward-looking rate shown beside the Hyperliquid funding countdown.

Partition keys: block_month and trader_prefix

The table is partitioned on (block_month, trader_prefix). block_month bounds the read in time; trader_prefix splits each month into 256 buckets by the first byte of the account address.

A single-account query must filter trader_prefix as well as trader. Trino derives no partition value from trader = 0x… on its own, so a trader-only predicate reads every one of the 256 buckets in range.

The bucket is a readable hex prefix rather than a hash precisely so you can state it yourself: it is the address’s first two hex characters. Where the address is not a literal, lower(substr(to_hex(trader), 1, 2)) gives the same value.

-- One account's positions at the latest complete hour
SELECT
  p.coin,
  p.position_side,
  p.position_size,
  p.position_notional_usd,
  p.funding_amount_usd
FROM hyperliquid.perp_positions_hourly AS p
WHERE p.block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1' day)
  AND p.trader_prefix = 'ab'                            -- required, or nothing prunes
  AND p.trader = 0xab5d5f0a3b1c2d3e4f5061728394a5b6c7d8e9f0
  AND p.block_hour = (
    SELECT MAX(block_hour)
    FROM hyperliquid.perp_positions_hourly
    WHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1' day)
      AND block_hour < DATE_TRUNC('hour', NOW())
  )
ORDER BY p.position_notional_usd DESC

coin does not prune: filtering it here still reads the full month across all 256 buckets. A market-grain question belongs in perp_market_metrics_hourly, which already carries open interest and funding per market-hour.

Entry price, unrealized PnL, and an account’s leverage and margin mode are not here: the venue-wide perp models deliberately do not reconstruct leverage. Market-level max_leverage, margin_mode and margin tiers are in perp_market_details.

Curated Data

Solana

Hyperliquid

Non-EVM Networks

Protocols

EVM Networks

Partner Data

Dune Index

hyperliquid.perp_positions_hourly

Table schema

A snapshot series, not a position history

valuation_price and open interest

Funding

Partition keys: block_month and trader_prefix

​Table schema

​A snapshot series, not a position history

​valuation_price and open interest

​Funding

​Partition keys: block_month and trader_prefix

Table schema

A snapshot series, not a position history

valuation_price and open interest

Funding

Partition keys: block_month and trader_prefix