Build a Hyperliquid trader leaderboard with the Pipes SDK

1. What we are building

A trader leaderboard is a small, real product: copy-trading apps, trader analytics, and exchange dashboards all need one. It is also a clean demonstration of what an indexer is for. You take the raw exchange tape, every fill on Hyperliquid, and reduce it into one row per trader: how much they traded and how much they made. The companion guide on reading Hyperliquid fills covers the data model; this one turns it into a pipeline you run.

The shape is four stages. SQD's Portal serves the hyperliquid-fills dataset; the Pipes SDK queries it and transforms each batch; the rows land in ClickHouse; and a SQL query reads the leaderboard back out.

The figures throughout are computed over a fixed window, hyperliquid-fills blocks 1,036,000,000 to 1,036,020,000 (about 22.9 minutes): 132,544 fills from 4,605 traders, $156.6M of notional volume. A running indexer keeps going from the head; the fixed window just makes the numbers here reproducible.

2. The query

The query declares which fields you want back and the block range to read. For a leaderboard we need the trader, the coin, price and size (to compute notional), and the two PnL inputs, closedPnl and fee. We also pull tid, the trade id, to use as a per-fill key in the table. An empty fill request matches every market, not just the majors.

import { HyperliquidFillsQueryBuilder } from '@subsquid/pipes/hyperliquid'

const FROM = 1_036_000_000 // hyperliquid-fills dataset starts at 750,000,000

const query = new HyperliquidFillsQueryBuilder()
  .addFields({
    block: { number: true, timestamp: true },
    fill: {
      user: true, coin: true, px: true, sz: true,
      tid: true, closedPnl: true, fee: true,
    },
  })
  // Empty request = every market on the venue (crypto, TradFi, HIP-3).
  // The range on addFill sets the start block.
  .addFill({ range: { from: FROM }, request: {} })

To follow specific wallets instead, the request takes a user filter; to narrow to certain markets, a coin filter. The range on .addFill() is required.

3. The transform

The source hands each batch to .pipe() as { blocks }, each block with a header and a fills array. We flatten the fills into rows, compute notional (price times size, not a native field), and tag each fill with an asset class so the leaderboard can later be split by market type.

import { hyperliquidFillsPortalSource } from '@subsquid/pipes/hyperliquid'

// Plain-name TradFi tickers on Hyperliquid (no cash:/xyz: prefix).
const TRADFI_PLAIN = new Set([
  'HOOD', 'GOOGL', 'TSM', 'NATGAS', 'PLATINUM', 'EWY', 'EWJ', 'CRWV', 'SNDK', 'SKHX',
])

function classifyAsset(coin: string): 'crypto' | 'tradfi' | 'hip3' {
  if (coin.startsWith('@')) return 'hip3'
  if (coin.startsWith('xyz:') || coin.startsWith('cash:')) return 'tradfi'
  if (TRADFI_PLAIN.has(coin)) return 'tradfi'
  return 'crypto'
}

const stream = hyperliquidFillsPortalSource({
  portal: 'https://portal.sqd.dev/datasets/hyperliquid-fills',
  query,
}).pipe(({ blocks }) => {
  const fills = blocks.flatMap((block) =>
    block.fills.map((fill) => ({
      block_number: block.header.number,
      timestamp: new Date(block.header.timestamp).toISOString(),
      user: fill.user,
      coin: fill.coin,
      tid: fill.tid,
      asset_class: classifyAsset(fill.coin),
      notional: fill.px * fill.sz,
      closed_pnl: fill.closedPnl,
      fee: fill.fee,
      sign: 1,
    })),
  )
  return { fills }
})

Hyperliquid block timestamps are milliseconds, so new Date(block.header.timestamp).toISOString() is the right conversion (no divide by 1000). The sign: 1 field is what makes reorgs cheap to undo, covered next.

4. The sink and schema

The rows land in a ClickHouse table. A CollapsingMergeTree keyed on a sign column makes reorg handling a matter of inserting offsetting rows rather than mutating data:

CREATE TABLE IF NOT EXISTS hl_fills (
    block_number  UInt64,
    timestamp     DateTime64(3, 'UTC'),
    user          LowCardinality(String),
    coin          LowCardinality(String),
    tid           UInt64,
    asset_class   LowCardinality(String),
    notional      Float64,
    closed_pnl    Float64,
    fee           Float64,
    sign          Int8
) ENGINE = CollapsingMergeTree(sign)
-- tid (the trade id) makes each fill a distinct object in the sorting key,
-- so a sign = -1 cancellation collapses exactly its own +1 row, even when a
-- trader has several fills in the same coin and block.
ORDER BY (user, coin, block_number, tid);

The target writes each batch and, on a rollback, removes rows past the safe cursor. That handler is the SDK's contract, not bespoke plumbing:

import { clickhouseTarget } from '@subsquid/pipes/targets/clickhouse'

stream.pipeTo(
  clickhouseTarget({
    client, // @clickhouse/client, configured from env
    onData: async ({ data, store }) => {
      if (data.fills.length) {
        await store.insert({ table: 'hl_fills', values: data.fills, format: 'JSONEachRow' })
      }
    },
    onRollback: async ({ safeCursor, store }) => {
      await store.removeAllRows({
        tables: ['hl_fills'],
        where: 'block_number > {latest:UInt64}',
        params: { latest: safeCursor.number },
      })
    },
  }),
)

That is the whole indexer: a query, a transform, and a sink. Point it at the dataset and it streams from block 1,036,000,000 to the head and then stays there, appending fills as new blocks arrive.

5. The leaderboard

With fills in the table, the leaderboard is one query. Net realized PnL is the booked profit on closing trades minus the cost of trading, closed_pnl - fee. One detail matters: because the table is a CollapsingMergeTree, a reorg cancels rolled-back fills by inserting matching rows with sign = -1. So every aggregate multiplies by sign rather than filtering sign = 1; the cancellation rows then net out the rolled-back fills immediately, without waiting for a background merge. The block_number bound scopes the read to the article's fixed window so the figures here reproduce; drop it for an all-time leaderboard over everything the indexer has ingested:

SELECT
  user,
  sum(sign)                                        AS fills,
  round(sum(sign * notional))                      AS volume_usd,
  round(sum(sign * closed_pnl) - sum(sign * fee))  AS net_pnl
FROM hl_fills
WHERE block_number BETWEEN 1036000000 AND 1036020000  -- the fixed window
GROUP BY user
HAVING sum(sign) > 0
ORDER BY net_pnl DESC
LIMIT 8

Over the fixed window, the result is dominated by one address: 0xcb71…7853 netted $369,875 on $2.7M of volume across 645 fills, more than the next seven traders combined. That long tail is the real shape of a leaderboard, and it falls straight out of the fills.

6. Crypto versus TradFi

Because every fill was tagged with an asset_class in the transform, the same table answers a question Hyperliquid is uniquely placed to raise: how much of the volume is actually crypto? The venue lists equities, commodities, and permissionless HIP-3 markets next to BTC and ETH, and they are not a rounding error.

SELECT
  asset_class,
  sum(sign)                   AS fills,
  round(sum(sign * notional)) AS volume_usd
FROM hl_fills
WHERE block_number BETWEEN 1036000000 AND 1036020000  -- the fixed window
GROUP BY asset_class
ORDER BY volume_usd DESC

In this window, crypto was 71.1% of volume, TradFi markets 24.2%, and HIP-3 listings 4.7%. Nearly a third of the notional traded was not crypto. Looking at the individual markets makes it concrete: the S&P 500 perp (xyz:SP500) was the third-largest market by volume, ahead of every altcoin, and half of the top ten markets were TradFi or permissionless rather than crypto.

This is the kind of cross-cutting view a leaderboard product wants and a per-address API cannot give you: it needs every fill, classified, in one place you can group and rank.

7. Why this is hard to do elsewhere

Two things make this practical with SQD and awkward without it. The first is the data: Hyperliquid's own info API returns fills per address and is rate-limited, so a market-wide leaderboard across thousands of traders and every market is not something it is built to hand you. The hyperliquid-fills dataset is that full tape as a single queryable source, from block 750,000,000 to the head.

The second is ownership. The Pipes SDK is open source and writes into your database with your schema, so the leaderboard is a table you can join, re-aggregate, and serve however you like, not a response shape someone else decided. The parts that are genuinely hard to get right, batching, the resumable cursor, and reorg handling, are the SDK's job. The PnL itself is read straight from the fills, with no oracle and no position snapshot.

The same pipeline shape works for the rest of SQD's datasets, so the leaderboard can sit beside EVM swap data or Solana activity in one store. For how this feeds a trading product, see the DeFi and trading solution page, and for the fills data model behind it, the guide on reading Hyperliquid perps data.