🤿Hyperliquid indexer

Real-time Hyperliquid deals indexing with data from May 2025

Video guide

You can start right with this one, we covered the main points

LogoBlockchain Explorators 🦜 ChatTelegram

General Description of the Hyperliquid Indexer

The Indexer is a Clickhouse database and complete analytical layer on top of Hyperliquid, collecting and normalizing all executed trades, orders, TWAP strategies, and user positions. At its core is a detailed table with raw data raw_node_fills_by_block, which stores every execution at the block level of the Hyperliquid network, including price, volume, PNL, fees, order types, liquidation data, TWAP identifiers, and position dynamics. On top of it, aggregating views (DM views) are built, which transform the stream of raw events into convenient analytical entities: executed orders, TWAP aggregates, daily trader statistics, metrics for each asset and each wallet, as well as current positions.

The uniqueness of the structure is that it models trading on Hyperliquid with maximum transparency and precise chronological order: each trade is recorded in chronological order, and the main views allow you to get both the micro-level (individual fill) and the macro-level (trader performance, ROI, win-rate, daily PnL, coin statistics, positions, etc.). This makes the database an ideal foundation for on-chain analytics, strategy backtesting, trader activity monitoring, liquidity analysis, leaderboard construction, and any research tasks related to the Hyperliquid market.

Access is provided through direct connection to the Clickhouse database without slowing layers like API and so on. You have the full power of SQL language and all capabilities of the database

Data is indexed from May 2025

How to Get Access

Cost is $300 per month, we provide a free trial for several days so you can thoroughly test everything and make a decision

For all questions, please contact https://t.me/ParrotExplorator

Working with the Indexer

Connecting via DBeaver

What is DBeaver? It's one of the most popular programs for working with databases. In short, you can connect to the database, edit it, write queries to it, and get results through the interface

DBeaver can be downloaded here https://dbeaver.io/download/. You need the free version (community edition), available for Windows, Mac OS, Linux. Create a connection and select Clickhouse

Connection settings for the demo version of the database with sample data:

Host:

148.251.79.237

Port:

8123

Database/Schema:

hyperliquid

User:

user_demo1

Password:

for_full_access_please_write_to_telegram_ParrotExplorator

Connection and Working from Python

We created a Google Colab Notebook with examples for you.

What is it? It's a service that allows you to run and edit Python code on Google's server absolutely free

https://colab.research.google.com/drive/1ZrJqqDq6ua6EvWXhHXX3eYe5oepo76vv?usp=sharing

But just in case, here's a simple example

pip install clickhouse-connect (installing the library for connecting to Clickhouse)

Now let's output the last 5 rows from the raw_node_fills_by_block table. The example uses a demo version of the database with sample data

Finding Smart Addresses. Example of Solving a Complex Task

This query calculates «pseudo-PnL» for SOL for each wallet based on executed orders. It takes the last SOL price from the last 3 days, recalculates for each order how much it would be in profit/loss at this price (conditional PnL), classifies orders as buy/sell, and then aggregates everything by wallets: how many trades, how many buy/sell, total and average PnL, volumes and PnL separately for buys and sells.

The author of the query is https://t.me/ruslan745. By the way, he helps write such queries for the indexer, Dune, and others, and has excellent SQL skills.

It's worth mentioning that you don't have to solve everything with SQL. Many people, including myself, prefer to export the data to Python in fractions of a second and then calculate it there in a way that's familiar to them.

Result:

Now let's break down what's happening here

  1. CTE last_price

What it does:

  • Takes the raw_node_fills_by_block table.

  • Filters only records from the last 3 days and only for the SOL coin.

  • Uses argMax(price, utc_fill_dttm) to get the price of the last fill in time (essentially, the last known SOL price over 3 days).

  • Returns one row:

    • coin = 'SOL'

    • last_price — the last SOL price (according to fills data).

This CTE is needed to calculate the PnL of orders relative to the current price.

  1. CTE trx

What's happening here:

  1. The dm_fulfilled_order (dfo) table is taken and joined with last_price by coin.

  2. Filter where coin = 'SOL' — we work only with SOL orders.

  3. Fields are formed:

    • order_time — order time (from utc_order_dttm).

    • wallet_address, coin, order_type — basic order fields.

  4. Determining order direction:

    The logic is as follows:

    • Open Long, Short > Long, Close Short → are considered as buy (direction = 'buy').

      • Increasing/transitioning to long or closing short.

    • Long > Short, Open Short, Close Long → are considered as sell (direction = 'sell').

      • Increasing/transitioning to short or closing long.

    • Other order types → direction = NULL (will be filtered out later).

  5. Calculating pseudo-PnL relative to last_price:

    Interpretation:

    • For logically "long" actions (buy group):

      • PnL ≈ size * (last_price - order_price)

        (if the current price is higher than the order price — positive, lower — negative).

    • For "short" actions (sell group):

      • PnL ≈ size * (order_price - last_price)

        (if the current price is lower than the order price — the short is in profit).

    • This is not realized PnL, but rather an estimate of how much the order is "worth" relative to the last price.

  6. Additionally passed through:

    • order_size, order_volume, order_price — order metrics from dm_fulfilled_order.

    • last_price — the last SOL price.

  7. order by utc_order_dttm — simply sorting within the CTE (doesn't affect the result of the final CTE trx on aggregation, but affects the order of viewing/debugging).

  1. Final SELECT

What's being done here:

  1. Source — CTE trx, i.e. already enriched order records for SOL.

  2. where direction is not null — we discard all orders for which we couldn't determine direction (didn't fall into our type lists).

  3. Grouping:

    → one row = one wallet for SOL.

  4. Metrics for each wallet:

    • all_trade — total number of orders (rows in trx) for this wallet.

    • buys — number of orders with direction = 'buy'.

    • sells — number of orders with direction = 'sell'.

    • avg_pnl — average PnL per order:sum(pnl) / count(*).

    • all_pnl — total PnL across all wallet orders (relative to last price).

    • buy_pnl — total PnL only for buy orders.

    • sell_pnl — total PnL only for sell orders.

    • all_volume — total trading volume (sum(order_volume)).

    • buy_volume — volume for buy orders.sell_volume — volume for sell orders.

  5. order by 7 desc — sorting by the 7th column, i.e.:

    • 1: wallet_address

    • 2: coin

    • 3: all_trade

    • 4: buys

    • 5: sells

    • 6: avg_pnl

    • 7: all_pnl

    This means the result is sorted by total PnL (all_pnl) in descending order — wallets with the highest "plus" on SOL come first.

1. Table hyperliquid.raw_node_fills_by_block

What it is

Raw log of all executed trades (fills) on Hyperliquid perpetual markets at block level.

One row = one fill for a given order, wallet and block.

Columns

  • utc_fill_dttm — execution timestamp (fill time) in UTC.

  • utc_fill_dt — calendar date of the fill (used for partitioning).

  • fill_id — internal execution ID (may be the same for the taker/maker pair of fills).

  • fill_hash — transaction/event hash of the fill in the Hyperliquid network.

  • fill_type — logical type of the fill / position effect:

    • Open Long, Open Short, Close Long, Close Short,

    • Long > Short, Short > Long (position flip),

    • Buy, Sell,

    • Liquidated ... (various liquidation types).

  • utc_block_dttm — timestamp of the block in which the fill was included.

  • block_id — Hyperliquid block identifier/number.

  • wallet_address — trader’s wallet address for which this fill is recorded.

  • coin — contract symbol (e.g. BTC, ETH, HYPE, @107, PURR/USDC, etc.).

  • price — execution price of the contract (quoted in the quote asset, usually a stablecoin).

  • size — traded size in base asset units (contracts/coins).

  • side — aggressor side:

    • B — buy (aggressive buyer),

    • A — ask/sell (aggressive seller).

  • start_position — position size in this contract before this fill (base asset units; sign indicates direction).

  • closed_pnl — realized PnL associated with the closed portion of the position in this fill (in quote currency).

  • order_id — Hyperliquid order ID the fill belongs to.

  • order_crossed_spread_flg — flag if the order crossed the spread:

    • true — aggressive/market-style order that hit the opposite best quote.

  • fee — execution fee (including possible maker rebate; can be positive or negative).

  • fee_token — token in which the fee was charged (e.g. USDC, USDH).

  • client_order_id — client-side order identifier set by the user/bot; may be NULL.

  • builder_fee — fee paid to the block builder, if applicable; may be NULL.

  • builder — builder’s address that received builder_fee; may be NULL.

  • liquidation_user — address of the liquidated user for liquidation events; NULL for normal trades.

  • liquidation_mark_px — mark price of the asset at liquidation time; NULL for regular trades.

  • liquidation_method — liquidation type/method (e.g. cross/isolated etc.); NULL for regular trades.

  • twap_id — TWAP order identifier if this fill is part of a TWAP; otherwise NULL.

2. View hyperliquid.dm_fulfilled_order

What it is

Order-level aggregation:

one row = one order (wallet_address + order_id) with all its fills aggregated in terms of total size, PnL and VWAP.

Source: hyperliquid.agg_fulfilled_order → aggregated → this DM view.

Columns

  • utc_order_dt — date (UTC) when the order was first filled (min date across its fills).

  • utc_order_dttm — timestamp (UTC) of the first fill of the order.

  • wallet_address — owner wallet address of the order.

  • order_id — Hyperliquid order ID.

  • order_type — logical type of the order based on its fills:

    • Open Long, Open Short, Close Long, Close Short, Buy, Sell,

    • Liquidated ..., etc. (anyMerge from underlying fills).

  • coin — contract symbol for the order.

  • twap_id — TWAP identifier if the order is part of a TWAP; otherwise NULL.

  • order_size — total traded size (sum of size over all fills of the order, in base asset units).

  • order_pnl — total realized PnL of the order (sum of closed_pnl).

  • order_volume — total notional volume (sum of size * price across fills).

  • order_price — average execution price (VWAP) of the order:

    • order_volume / order_size.

  • cnt_fill_id — number of fills used to execute this order.

3. View hyperliquid.dm_fulfilled_twap

What it is

TWAP-level aggregation.

One row = one TWAP (wallet_address + twap_id) with its size, PnL, volume and time window.

Source: hyperliquid.agg_fulfilled_twap.

Columns

  • wallet_address — wallet executing the TWAP.

  • twap_id — TWAP strategy/order ID.

  • order_type — logical type of the TWAP:

    • e.g. Open Long, Open Short, Close Long, Close Short, Buy, Sell, etc.

  • coin — contract symbol traded by the TWAP.

  • twap_size — total executed size of the TWAP in base asset units (sum of size across its fills).

  • twap_pnl — total realized PnL across all TWAP fills.

  • twap_volume — total notional volume (sum(size * price)).

  • twap_avg_price — twap_pnl / nullIf(twap_volume, 0):

    • effectively average PnL per unit of volume (a ROI-like metric),

    • not a conventional execution price,

    • NULL if twap_volume = 0.

  • cnt_fill_id — number of fills belonging to this TWAP.

  • cnt_order_id — number of unique underlying orders that make up this TWAP.

  • utc_twap_start_dt — date of the first TWAP fill (min utc_fill_dt).

  • utc_twap_end_dt — date of the last TWAP fill (max utc_fill_dt).

  • utc_twap_start_dttm — timestamp of the first TWAP fill (min utc_fill_dttm).

  • utc_twap_end_dttm — timestamp of the last TWAP fill (max utc_fill_dttm).

4. View hyperliquid.dm_perpetual_wallet_coin_stat

What it is

Per-coin trading stats for each perpetual wallet.

One row = one wallet_address + one coin.

Metrics are computed only on closing and liquidation orders.

Source: hyperliquid.dm_fulfilled_order

(WHERE order_type IN (...) limited to Close/Liquidated types).

Columns

  • wallet_address — wallet address.

  • coin — perpetual contract symbol.

Aggregate sums

  • total_pnl — total realized PnL for this coin (money PnL, rounded to 2 decimals).

  • total_volume — total traded notional volume for this coin, rounded to 2 decimals.

  • cnt_unique_orders — count of orders included in the stats (only Close/Liquidated).

  • cnt_trade_days — count of unique trading days with such orders.

Percentage metrics

  • prct_win_rate — share of profitable orders in %:

    countIf(order_pnl > 0) / count(*) * 100.

  • prct_wallet_roi — overall ROI for this coin in % of total volume:

    sum(order_pnl) / sum(order_volume) * 100.

  • prct_avg_order_roi — average per-order ROI in %:

    • for each order: (order_pnl / order_volume) * 100,

    • then average over orders.

Per-order and per-day averages

  • avg_trade_cnt_per_day — average number of orders per trading day for this coin.

  • avg_volume — average order notional volume:

    total_volume / cnt_unique_orders.

  • avg_pnl — average PnL per order:

    total_pnl / cnt_unique_orders.

  • avg_daily_volume — average daily notional volume:

    total_volume / cnt_trade_days.

  • avg_daily_pnl — average daily PnL:

    total_pnl / cnt_trade_days.

Average profitable / unprofitable order PnL

  • avg_profitable_pnl — mean PnL among profitable orders (order_pnl > 0).

  • avg_unprofitable_pnl — mean PnL among losing orders (order_pnl < 0).

Order-level ROI quantiles (in %)

All based on (order_pnl / order_volume) * 100:

  • p0_prct_avg_order_roi — 0th percentile (minimum per-order ROI).

  • p20_prct_avg_order_roi — 20th percentile.

  • p40_prct_avg_order_roi — 40th percentile.

  • p60_prct_avg_order_roi — 60th percentile.

  • p80_prct_avg_order_roi — 80th percentile.

  • p100_prct_avg_order_roi — 100th percentile (maximum per-order ROI).

Service field

  • last_utc_order_dt — last date (UTC) when this wallet had a close/liquidation order for this coin.

5. View hyperliquid.dm_perpetual_wallets

What it is

Wallet-level aggregation across all perpetual coins.

One row = one wallet_address.

Metrics are conceptually the same as in dm_perpetual_wallet_coin_stat, but aggregated over all coins.

Source: hyperliquid.dm_fulfilled_order (same Close/Liquidated order filter).

Columns

  • wallet_address — wallet address.

Aggregate sums

  • total_pnl — total realized PnL across all coins.

  • total_volume — total traded notional volume across all coins.

  • cnt_unique_coins — number of distinct contracts traded.

  • cnt_unique_orders — count of orders (Close/Liquidated) included.

  • cnt_trade_days — number of unique trading days.

Percentage metrics

  • prct_win_rate — % of profitable orders for the wallet.

  • prct_wallet_roi — total ROI across all coins in % of total volume.

  • prct_avg_order_roi — average per-order ROI in %.

Per-order and per-day averages

  • avg_trade_cnt_per_day — average number of orders per day.

  • avg_volume — average order notional volume.

  • avg_pnl — average PnL per order.

  • avg_daily_volume — average daily notional volume.

  • avg_daily_pnl — average daily PnL.

Average profitable / unprofitable order PnL

  • avg_profitable_pnl — mean profit among winning orders.

  • avg_unprofitable_pnl — mean loss among losing orders.

Order-level ROI quantiles (in %)

Same logic as coin-level, but across all orders of the wallet:

  • p0_prct_avg_order_roi

  • p20_prct_avg_order_roi

  • p40_prct_avg_order_roi

  • p60_prct_avg_order_roi

  • p80_prct_avg_order_roi

  • p100_prct_avg_order_roi

Service field

  • last_utc_order_dt — last date of a close/liquidation order for the wallet.

6. View hyperliquid.dm_perpetual_wallets_intro_day

What it is

Daily-level aggregation per wallet.

First, intermediate daily stats per wallet are built (CTE prep), then these are summed/averaged across days.

Compared to dm_perpetual_wallets, this view focuses on day-level behavior, not directly per-order behavior.

Columns

  • wallet_address — wallet address.

Aggregated metrics (over days)

Coming from daily aggregates:

  • total_pnl — sum of daily PnL over all days.

  • total_volume — sum of daily notional volume over all days.

  • avg_daily_pnl — average of daily average PnL per order:

    • within a day: avg_daily_pnl = daily_pnl / cnt_unique_orders,

    • then avg() over days.

  • avg_daily_volume — analogous for volume:

    • within a day: avg_daily_volume = daily_volume / cnt_unique_orders,

    • then avg() over days.

  • cnt_unique_orders — total number of orders over all days (sum of daily cnt_unique_orders).

  • cnt_trade_days — number of trading days (count of distinct utc_order_dt).

7. View hyperliquid.dm_wallet_position

What it is

Current (latest known) perpetual position size per wallet and coin.

One row = one wallet_address + one coin.

Source: hyperliquid.agg_wallet_position, which incrementally builds position from raw_node_fills_by_block using start_position ± size logic depending on fill_type.

Columns

  • wallet_address — wallet address.

  • coin — contract symbol.

  • last_position_size — latest position size for this coin:

    • 0 — long position,

    • < 0 — short position,

    • 0 or NULL — flat/no position or unknown.

  • last_position_update_dttm — timestamp (UTC) of the last fill that affected this position.

PreviousAccess via x402 PaymentsNextData Streaming Node

Last updated