> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dune.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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.

export const PremiumDatasetAccessCard = ({href = "https://dune.com/enterprise#contact-form", note = null}) => <Card title="Gated dataset" icon="lock" href={href}>
    Querying this dataset requires an entitlement on your workspace. See <a href="/data-catalog/overview#access-tiers-public-vs-gated-datasets">access tiers</a>, or contact the Dune team to enable access.
    {note && <><br /><br />{note}</>}
  </Card>;

`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`](/data-catalog/curated/perpetuals/hyperliquid/perp-positions-hourly). [`perp_trades`](/data-catalog/curated/rwa/activity/perp-trades) tells you what an account *did*; this table tells you what it *held*.

<PremiumDatasetAccessCard />

## 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.

<Warning>
  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.
</Warning>

## 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:

```sql theme={null}
-- 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`](/data-catalog/curated/rwa/activity/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:

```sql theme={null}
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.

```sql theme={null}
-- 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`](/data-catalog/curated/rwa/registry/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.

<Note>
  Entry price, unrealized PnL, and an account's leverage and margin mode are not here — [`perp_trades`](/data-catalog/curated/rwa/activity/perp-trades) carries leverage and margin mode per fill, and market-level `max_leverage` and `margin_mode` are in [`rwa_hyperliquid.markets`](/data-catalog/curated/rwa/registry/hyperliquid-markets). Vaults are ordinary accounts here and are not flagged.
</Note>