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

# hyperliquid.perp_positions_hourly

> Per-account Hyperliquid perp position snapshots 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>;

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

<PremiumDatasetAccessCard />

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

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

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:

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

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

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.

```sql theme={null}
-- 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`](/data-catalog/curated/perpetuals/hyperliquid/perp-market-metrics-hourly), which already carries open interest and funding per market-hour.

<Note>
  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`](/data-catalog/curated/perpetuals/hyperliquid/perp-market-details).
</Note>