rwa_hyperliquid.perp_positions_hourly

Per-account position snapshots on RWA perpetual markets, taken at each hourly funding round, with signed size, USD notional, and the funding settled on the position.

rwa_hyperliquid.perp_positions_hourly is the per-account position surface for RWA perpetuals. Grain: one row per (trader, coin, block_hour) — every account position open in a Hyperliquid HIP-3 RWA market at the single global funding round that clears every market and account each hour. Market classification is already joined on.

It is the RWA-scoped subset of hyperliquid.perp_positions_hourly. perp_trades tells you what an account did; this table tells you what it held.

Table schema

Column	Type	Description
block_month	DATE	First partition key. Filter it to bound a read in time
block_date	DATE	Day of block_hour. Not a partition key
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	Builder DEX short code. Never hyperliquid — no first-party market is classified real-world
coin	VARCHAR	Full market id in dex:SYMBOL form. The join key to rwa_hyperliquid.markets
market_symbol	VARCHAR	coin with the dex: prefix stripped. Not unique across dexes, so never join or group on it alone
asset_id	BIGINT	Numeric HIP-3 market id. BIGINT, since HIP-4 ids exceed 1e8
asset_class	VARCHAR	Shared RWA product taxonomy. Never null
asset_type	VARCHAR	Finer classification within asset_class. Never null
underlying_ticker	VARCHAR	Ticker of the referenced real-world instrument. Null where no listed instrument exists (indices, commodities, fx, pre-IPO names)
trader	VARBINARY	The account holding the position, as raw bytes
trader_prefix	VARCHAR	Second partition key: the first byte of trader as two lowercase hex characters. 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 to the round — the same price perp_metrics_hourly strikes open interest at
position_notional_usd	DOUBLE	abs(position_size) * valuation_price — unsigned exposure, so read position_side for direction
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
metadata_last_updated	DATE	Date a curated cell on this market last changed. The pushable form of the _updated_at floor — says nothing about the position
_updated_at	TIMESTAMP(3) WITH TIME ZONE	The later of the position row’s own watermark and the market map’s last_updated

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. To detect a close, compare the set of (trader, coin) keys between two hours rather than looking for a terminating row.

This also means positions are not reconstructible from fills: perp_trades publishes both legs of every match and so is per-account complete, but it reports fills, not what an account held at an hour boundary.

RWA history starts 2025-10-13, later than the venue-wide funding feed floor, because RWA HIP-3 markets listed after it.

The newest hour is missing rather than empty until its funding round is ingested. Filter block_hour < date_trunc('hour', now()) for complete data, and never read a zero count at the current hour as zero open interest.

Single-account reads must filter trader_prefix

The physical table this view reads is partitioned on (block_month, trader_prefix). 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 instead of one.

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, lowercase:

-- One account's RWA position snapshots this month, complete hours only
SELECT
  coin,
  underlying_ticker,
  asset_class,
  position_side,
  position_size,
  position_notional_usd,
  funding_amount_usd
FROM rwa_hyperliquid.perp_positions_hourly
WHERE block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1' day)
  AND trader_prefix = 'ab'                              -- required, or nothing prunes
  AND trader = 0xab5d5f0a3b1c2d3e4f5061728394a5b6c7d8e9f0
  AND block_hour < DATE_TRUNC('hour', NOW())
ORDER BY position_notional_usd DESC

Where the address is not a literal, lower(substr(to_hex(trader), 1, 2)) gives the same value. to_hex returns uppercase and the partition values are lowercase, so an uppercase prefix matches nothing.

coin does not prune here. Market-grain questions belong in perp_metrics_hourly, which already carries open interest and funding per market-hour.

Rolling up reproduces open interest

Both tables value a position at the same carried-forward trade price, so aggregating to (coin, block_hour) reproduces perp_metrics_hourly.open_interest_usd and open_positions 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 rwa_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_hour < DATE_TRUNC('hour', NOW())
GROUP BY 1, 2

That sum counts both sides — every long is matched by a short — which is the convention the Hyperliquid UI uses. Classical one-sided open interest is half of it. Use perp_metrics_hourly when the market-level figure is all you need; this table is for when you need to decompose it by account.

Because valuation_price 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.

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.

-- Who paid and who collected funding on RWA equity perps last week
SELECT
  '0x' || lower(to_hex(trader)) AS trader,
  SUM(funding_amount_usd)       AS net_funding_usd,
  AVG(position_notional_usd)    AS avg_notional_usd,
  COUNT(*)                      AS position_hours
FROM rwa_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_hour < DATE_TRUNC('hour', NOW())
  AND asset_class = 'equities'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 25

Classification is joined live

asset_class, asset_type, and underlying_ticker come from rwa_hyperliquid.markets on every read, so reclassifying a market lands immediately with nothing to backfill. RWA scope is that classification itself, and it is default-deny: a market appears only once its underlying is classified as real-world. Crypto, crypto-dominance indices, GPU-compute indices, and any market that has listed but not been classified are all absent — a market missing here is unclassified, not pending. fx is in scope.

Entry price, unrealized PnL, and an account’s leverage and margin mode are not here — perp_trades carries leverage and margin mode per fill, and market-level max_leverage and margin_mode are in rwa_hyperliquid.markets. Vaults are ordinary accounts here and are not flagged.