Hyperliquid SQL: query every fill, liquidation and transfer

Ironflow indexes every Hyperliquid fill, liquidation, funding rate and transfer from its own nodes and Hyperliquid's archive, and lets you query all of it with read-only SQL: over HTTP at /v1/query, or from an AI agent with the MCP run_query tool. This page covers the tables, the limits per plan, how to keep a query fast, and eight queries we ran against the live API.

The short version#

• Endpoint: POST https://api.ironflow.sh/v1/query with {"sql": "..."}, or GET /v1/query?sql= for clients that only fetch URLs.
• Dialect: ClickHouse SQL, read-only, one statement, tables in the hl database.
• Data: every fill and liquidation since 25 May 2025, plus funding, mark and oracle prices, open interest, transfers and per-wallet daily totals.
• No key needed: the last 24 hours, 5 seconds per query. A free key reaches 30 days, paid plans up to 1 year.
• Schema: GET /v1/query/schema returns every table, column, tip, example and your own limits.

1. Your first query#

With curl, URL-encoded in a GET. This totals the liquidated USD per market over the last 24 hours:

curl -G "https://api.ironflow.sh/v1/query" --data-urlencode \
  "sql=SELECT market, round(sum(notional)) AS usd FROM hl.liquidations WHERE ts > now() - INTERVAL 1 DAY GROUP BY market ORDER BY usd DESC LIMIT 5"

The same query as a POST, which is easier for long SQL:

curl -X POST "https://api.ironflow.sh/v1/query" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT market, round(sum(notional)) AS usd FROM hl.liquidations WHERE ts > now() - INTERVAL 1 DAY GROUP BY market ORDER BY usd DESC LIMIT 5"}'

The answer lists the columns with their ClickHouse types, the rows as arrays, what the query read and the query seconds you have left this hour. This one is from 7 November 2026:

{
  "columns": [{"name": "market", "type": "LowCardinality(String)"}, {"name": "usd", "type": "Float64"}],
  "rows": [["BTC-PERP", 13799106], ["ZEC-PERP", 2678685], ["XRP-PERP", 1982851], ["xyz:SOXL-PERP", 1494720], ["xyz:SPCX-PERP", 1380007]],
  "row_count": 5,
  "truncated": false,
  "stats": {"elapsed_ms": 6.957377, "rows_read": 72298, "bytes_read": 1840641},
  "limits": {"history_days": 1, "max_execution_seconds": 5, "max_rows": 1000, "budget_seconds_per_hour": 60},
  "budget_remaining_seconds": 59.991042295
}

In Python, with requests:

import requests

sql = """
SELECT hour, round(volume_usd) AS volume_usd, round(liquidation_usd) AS liquidated
FROM hl.market_hourly
WHERE market = 'BTC-PERP' AND hour >= now() - INTERVAL 6 HOUR
ORDER BY hour
"""
r = requests.post("https://api.ironflow.sh/v1/query", json={"sql": sql})
r.raise_for_status()
body = r.json()
cols = [c["name"] for c in body["columns"]]
for row in body["rows"]:
    print(dict(zip(cols, row)))

With a key, send it as Authorization: Bearer $IRONFLOW_API_KEY. The query and the response stay the same; only the history window and the limits change.

2. Tables#

Fifteen views in the hl database. Every market column uses Ironflow's symbols: BTC-PERP, HYPE/USDC for spot, xyz:NVDA-PERP for a HIP-3 perp. HIP-3 tables carry the builder in dex, empty for native markets.

Column lists and descriptions come from GET /v1/query/schema, or in SQL with DESCRIBE hl.liquidations. Two notes on fills: is_liquidation is not set on fills from 1 November 2025 to 21 August 2026, so use hl.liquidations for liquidations across that range; and direction and hash are recorded from 1 August 2026.

3. Limits per plan#

Your plan sets every limit and ClickHouse enforces it. Rows older than the history window are filtered out of every table, so a query over a longer range returns less, not an error.

• Only SELECT, WITH, DESCRIBE, SHOW and EXPLAIN, one statement per request, at most 20,000 characters, without a FORMAT clause.
• A query past its time limit answers 504 QUERY_TIMEOUT. An hour's budget used up answers 429 QUERY_BUDGET_EXCEEDED. A query that would read too much answers 400 QUERY_TOO_EXPENSIVE.
• Plans and prices are on the pricing table. When a query hits a limit, the error names the plan that lifts it.

4. Fast queries#

• Filter on time first. ts is what limits the scan on hl.fills and hl.trades: the last few hours are cheap, older windows cost more. A market filter cuts the work, but every row in the time window is still read.
• Totals over days or months: read hl.market_hourly (per market) or hl.wallet_daily (per wallet). A year of every market in hl.market_hourly is a few million rows; hl.fills holds about 15 million a day.
• One wallet: hl.wallet_fills is sorted by address and reads a few hundred thousand rows instead of every fill in the window. Addresses are lowercase.
• Counting trades: each trade is two rows in hl.fills. Use sum(notional) / 2 there, or sum(notional) on hl.trades.
• Aggregate in SQL. Results stop at the row limit (1,000 without a key), and truncated says when they did.
• Useful functions: toStartOfInterval(ts, INTERVAL 5 MINUTE), argMax(price, ts) for the last value, sumIf, countIf, uniqExact(address), quantile(0.9)(x).

5. Eight example queries#

Each one filters to one market (or one token) and a short window, so it runs without a key. On 7 November 2026 each returned rows in 9 to 201 ms, reading at most 1.7 million rows.

SELECT toStartOfInterval(ts, INTERVAL 5 MINUTE) AS t,
       argMin(price, ts) AS open, max(price) AS high, min(price) AS low, argMax(price, ts) AS close,
       round(sum(notional)) AS volume_usd, count() AS trades
FROM hl.trades
WHERE market = 'BTC-PERP' AND ts > now() - INTERVAL 1 HOUR
GROUP BY t
ORDER BY t

SELECT hour, round(taker_buy_usd) AS taker_buy, round(taker_sell_usd) AS taker_sell,
       round(taker_buy_usd - taker_sell_usd) AS net, round(liquidation_usd) AS liquidated
FROM hl.market_hourly
WHERE market = 'ETH-PERP' AND hour >= now() - INTERVAL 12 HOUR
ORDER BY hour

SELECT ts, address, if(side = 'sell', 'long', 'short') AS position, price, round(notional) AS usd
FROM hl.liquidations
WHERE market = 'BTC-PERP' AND ts > now() - INTERVAL 1 DAY
ORDER BY notional DESC
LIMIT 10

SELECT address, round(sumIf(notional, side = 'buy')) AS bought,
       round(sumIf(notional, side = 'sell')) AS sold, count() AS fills
FROM hl.fills
WHERE market = 'HYPE-PERP' AND ts > now() - INTERVAL 1 HOUR AND is_taker
GROUP BY address
ORDER BY bought + sold DESC
LIMIT 10

SELECT toStartOfHour(ts) + INTERVAL 1 HOUR AS settled_at, argMax(rate, ts) AS hourly_rate,
       round(argMax(rate, ts) * 24 * 365 * 100, 2) AS apr_pct
FROM hl.funding
WHERE market = 'BTC-PERP' AND ts > now() - INTERVAL 12 HOUR
GROUP BY settled_at
ORDER BY settled_at

SELECT toStartOfFiveMinutes(m.ts) AS t,
       round(avg((m.price - o.price) / o.price) * 10000, 2) AS mark_vs_oracle_bps
FROM hl.mark_prices AS m
INNER JOIN hl.oracle_prices AS o ON o.market = m.market AND o.ts = m.ts
WHERE m.market = 'xyz:NVDA-PERP' AND o.market = 'xyz:NVDA-PERP'
  AND m.ts > now() - INTERVAL 1 HOUR AND o.ts > now() - INTERVAL 1 HOUR
GROUP BY t
ORDER BY t

SELECT ts, address AS sender, counterparty AS receiver, amount
FROM hl.transfers
WHERE type = 'send' AND token = 'USDC' AND signed_amount < 0 AND ts > now() - INTERVAL 1 HOUR
ORDER BY amount DESC
LIMIT 10

SELECT address, round(sum(realized_pnl_usd)) AS pnl, round(sum(volume_usd)) AS volume, sum(fills) AS fills
FROM hl.wallet_daily
WHERE market = 'BTC-PERP' AND day >= today() - 1
GROUP BY address
ORDER BY pnl DESC
LIMIT 10

In the funding query each hour's last rate is the one settled at the next hour boundary; the newest row is the live rate of the hour in progress. In the liquidation query a sell closes a long and a buy closes a short, and one liquidation can take several fills.

6. From an AI agent#

Ironflow's MCP server at https://mcp.ironflow.sh/mcp has two tools for this: describe_data returns the tables, columns, tips and your limits, and run_query runs one query with the same limits as the API. No key is needed to start. In Claude Code:

claude mcp add --transport http ironflow https://mcp.ironflow.sh/mcp

Then ask in plain words, for example “Which wallets took the most HYPE liquidity in the last hour?”. The agent reads the schema, writes the SQL and shows the rows. Setup for other clients is in the AI agents guide.

Questions#

Related guides#

• Hyperliquid historical data: API, S3 archive, SQL and free downloads
• Hyperliquid trades and fills data
• Hyperliquid liquidation data: history, levels and API
• Hyperliquid funding rates: formula, history and API

References#

• Ironflow docs: Run SQL (POST). Request body, limits table and error codes.
• Ironflow docs: Run SQL (GET). The same query from a URL.
• Ironflow docs: Query schema. Every table and column, with your own limits.
• ClickHouse docs: SELECT. The query syntax.
• ClickHouse docs: Functions. Time, aggregate and string functions.