Hyperliquid query examples

Working ClickHouse SQL against the Supanode Hyperliquid Historical Data Indexer: trader leaderboards, maker/taker split, liquidations, TWAP execution quality, builder-code flow, and nanosecond block timing.

// updated 2026-08-22

Queries you can paste, each written against the real columns in the Table reference.

NOTE

Everything starts from raw_node_fills_by_block. One row per fill, partitioned by utc_fill_dt. Filter on that date column in PREWHERE before anything else - the table is 3.13 billion rows and 225 GB.

WARNING

Amounts are already in USD. closed_pnl, fee, builder_fee and priority_gas are Float64 in USD - no base-unit division. Notional is price * size.

Check the enum values first

side and fill_type are plain strings. Confirm what they actually contain before you hard-code a filter:

SELECT side, fill_type, count() AS fills
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt = today()
GROUP BY side, fill_type
ORDER BY fills DESC

Largest fills in the last 24 hours

SELECT
    utc_fill_dttm,
    fill_id,
    wallet_address,
    coin,
    side,
    price,
    size,
    price * size AS notional,
    closed_pnl,
    fee,
    liquidation_user
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 1
ORDER BY notional DESC
LIMIT 100

Volume by market

SELECT
    coin,
    count()                AS fills,
    sum(price * size)      AS notional_volume,
    uniq(wallet_address)   AS unique_traders,
    avg(price * size)      AS avg_fill_notional
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 1
GROUP BY coin
ORDER BY notional_volume DESC
LIMIT 50

Maker versus taker flow

order_crossed_spread_flg is the taker flag - it is set when the order crossed the spread rather than resting on the book.

SELECT
    coin,
    countIf(order_crossed_spread_flg)      AS taker_fills,
    countIf(NOT order_crossed_spread_flg)  AS maker_fills,
    sumIf(price * size, order_crossed_spread_flg)     AS taker_notional,
    sumIf(price * size, NOT order_crossed_spread_flg) AS maker_notional,
    round(100 * countIf(order_crossed_spread_flg) / count(), 2) AS taker_pct
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt = today()
GROUP BY coin
ORDER BY taker_notional DESC
LIMIT 25

Trader PnL leaderboard

SELECT
    wallet_address,
    count()                          AS fills,
    countIf(closed_pnl != 0)         AS closing_fills,
    sum(closed_pnl)                  AS realized_pnl,
    sum(price * size)                AS notional_volume,
    sum(fee)                         AS fees_paid,
    countIf(closed_pnl > 0)          AS wins,
    countIf(closed_pnl < 0)          AS losses,
    round(100 * countIf(closed_pnl > 0) / nullIf(countIf(closed_pnl != 0), 0), 2) AS win_rate_pct,
    uniq(coin)                       AS markets
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 30
GROUP BY wallet_address
HAVING closing_fills >= 50
ORDER BY realized_pnl DESC
LIMIT 100

TIP

The same leaderboard, precomputed. view_perpetual_wallet already holds total_pnl, total_volume, win_count, cnt_trade_days and ROI quantiles per wallet - see the query further down. Use it when you want the answer, and the raw table when you want to define the window yourself.

Liquidations

A liquidation carries a non-null liquidation_user, together with the mark price and the method used.

SELECT
    utc_fill_dttm,
    coin,
    liquidation_user,
    liquidation_method,
    liquidation_mark_px,
    side,
    price,
    size,
    price * size AS notional,
    closed_pnl
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 1
WHERE liquidation_user IS NOT NULL
ORDER BY notional DESC
LIMIT 100

Rolled up by market over a week:

SELECT
    coin,
    count()                        AS liquidation_fills,
    uniq(liquidation_user)         AS wallets_liquidated,
    sum(price * size)              AS liquidated_notional,
    sum(closed_pnl)                AS realized_pnl
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 7
WHERE liquidation_user IS NOT NULL
GROUP BY coin
ORDER BY liquidated_notional DESC

TWAP execution quality

Fills belonging to the same TWAP share a twap_id. Grouping on it reconstructs the whole execution.

SELECT
    twap_id,
    wallet_address,
    coin,
    side,
    count()                        AS fills,
    sum(size)                      AS total_size,
    sum(price * size) / sum(size)  AS vwap,
    min(utc_fill_dttm)             AS started,
    max(utc_fill_dttm)             AS ended,
    dateDiff('minute', min(utc_fill_dttm), max(utc_fill_dttm)) AS duration_min
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 1
WHERE twap_id IS NOT NULL
GROUP BY twap_id, wallet_address, coin, side
ORDER BY total_size DESC
LIMIT 50

Compare a TWAP's own VWAP against the market VWAP over the same window to score the execution:

WITH twaps AS (
    SELECT
        twap_id,
        coin,
        sum(price * size) / sum(size) AS twap_vwap,
        sum(size)                     AS twap_size,
        min(utc_fill_dttm)            AS t0,
        max(utc_fill_dttm)            AS t1
    FROM hyperliquid.raw_node_fills_by_block
    PREWHERE utc_fill_dt = today()
    WHERE twap_id IS NOT NULL
    GROUP BY twap_id, coin
    HAVING count() >= 5
)
SELECT
    t.twap_id,
    t.coin,
    t.twap_size,
    t.twap_vwap,
    m.market_vwap,
    round(1e4 * (t.twap_vwap - m.market_vwap) / m.market_vwap, 2) AS slippage_bps
FROM twaps AS t
INNER JOIN
(
    SELECT coin, sum(price * size) / sum(size) AS market_vwap
    FROM hyperliquid.raw_node_fills_by_block
    PREWHERE utc_fill_dt = today()
    GROUP BY coin
) AS m
ON t.coin = m.coin
ORDER BY abs(slippage_bps) DESC
LIMIT 50

Builder-code flow

builder identifies the front-end or API that routed the order, and builder_fee is what it earned.

SELECT
    builder,
    count()             AS fills,
    uniq(wallet_address) AS wallets,
    sum(price * size)   AS routed_notional,
    sum(builder_fee)    AS builder_revenue
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 7
WHERE builder IS NOT NULL
GROUP BY builder
ORDER BY routed_notional DESC
LIMIT 25

Priority gas spend

SELECT
    wallet_address,
    count()             AS fills,
    sum(priority_gas)   AS priority_gas_usdc,
    max(priority_gas)   AS biggest_single,
    sum(price * size)   AS notional
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 7
WHERE priority_gas IS NOT NULL AND priority_gas > 0
GROUP BY wallet_address
ORDER BY priority_gas_usdc DESC
LIMIT 25

Nanosecond block timing

utc_block_dttm is DateTime64(9) - nanosecond resolution on the block, while utc_fill_dttm is millisecond resolution on the fill. Use the block timestamp with block_tx_idx for ordering work.

SELECT
    block_id,
    utc_block_dttm,
    count()                                  AS fills_in_block,
    uniq(coin)                               AS markets,
    min(block_tx_idx)                        AS first_tx_idx,
    max(block_tx_idx)                        AS last_tx_idx,
    sum(price * size)                        AS block_notional
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt = today()
GROUP BY block_id, utc_block_dttm
ORDER BY fills_in_block DESC
LIMIT 25

Time between consecutive blocks, in milliseconds:

SELECT
    block_id,
    utc_block_dttm,
    round((toUnixTimestamp64Nano(utc_block_dttm)
         - toUnixTimestamp64Nano(any(utc_block_dttm) OVER (ORDER BY block_id ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))) / 1e6, 3) AS gap_ms
FROM
(
    SELECT DISTINCT block_id, utc_block_dttm
    FROM hyperliquid.raw_node_fills_by_block
    PREWHERE utc_fill_dt = today()
    ORDER BY block_id
    LIMIT 5000
)
ORDER BY block_id

Precomputed wallet views

SELECT
    wallet_address,
    total_pnl,
    total_volume,
    cnt_unique_orders,
    win_count,
    total_count,
    round(100 * win_count / nullIf(total_count, 0), 2) AS win_rate_pct,
    cnt_unique_coins,
    cnt_trade_days,
    last_utc_order_dt
FROM hyperliquid.view_perpetual_wallet
WHERE total_count > 100
ORDER BY total_pnl DESC
LIMIT 100

Open positions right now, by market:

SELECT
    coin,
    countIf(last_position_size > 0)  AS longs,
    countIf(last_position_size < 0)  AS shorts,
    sumIf(last_position_size, last_position_size > 0)  AS long_size,
    sumIf(last_position_size, last_position_size < 0)  AS short_size
FROM hyperliquid.view_wallet_position
WHERE last_position_size != 0
GROUP BY coin
ORDER BY long_size + abs(short_size) DESC

Orders instead of fills

agg_fulfilled_order collapses every fill of an order into one row. Its aggregate columns must be read through sum().

SELECT
    wallet_address,
    coin,
    count()           AS orders,
    sum(size)         AS filled_size,
    sum(volume)       AS filled_volume_usd,
    sum(closed_pnl)   AS realized_pnl
FROM hyperliquid.agg_fulfilled_order
PREWHERE utc_first_fill_dt >= today() - 30
GROUP BY wallet_address, coin
ORDER BY filled_volume_usd DESC
LIMIT 50

Performance notes

Always filter utc_fill_dt in PREWHERE. It is the partition key; everything else is a full scan.
Add a coin filter whenever the question is about one market - it cuts the scan by an order of magnitude.
Aggregate before joining. The two-step TWAP query above joins two aggregates, not two raw scans.
agg_fulfilled_order keeps 90 days. Longer studies run on raw_node_fills_by_block.

Check the plan before a heavy scan:

EXPLAIN indexes = 1
SELECT count() FROM hyperliquid.raw_node_fills_by_block WHERE utc_fill_dt = today()

Get started

Provision access or start a trial: @supanode_tgs.