rwa_hyperliquid.perp_positions_hourly

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.

Curated Data

Solana

Hyperliquid

Non-EVM Networks

Protocols

EVM Networks

Partner Data

Dune Index

rwa_hyperliquid.perp_positions_hourly

Table schema

A snapshot series, not a position history

Single-account reads must filter trader_prefix

Rolling up reproduces open interest

Funding

Classification is joined live

​Table schema

​A snapshot series, not a position history

​Single-account reads must filter trader_prefix

​Rolling up reproduces open interest

​Funding

​Classification is joined live

Table schema

A snapshot series, not a position history

Single-account reads must filter trader_prefix

Rolling up reproduces open interest

Funding

Classification is joined live