Perpetual fills and wallet activity. Ready for your SQL.
SELECT wallet_address,
count() AS fills
FROM raw_node_fills_by_block
PREWHERE utc_fill_dt = '2026-09-01'
GROUP BY wallet_addressInvestigate perpetual fills, wallets and execution context in ClickHouse without building your own fill warehouse. Query raw fill history from 27 July 2025.
Research trade activity, build wallet screens and compare realized PnL across a bounded historical window.
Inspect liquidation and TWAP context, maker/taker activity and builder attribution alongside the fill.
Raw fills and derived surfaces answer different questions and have different history rules. For the current book, explore the separate Hyperliquid Orderbook streaming product.
Use a ClickHouse client such as DBeaver or the Python client. This example ranks BTC wallets by traded notional within one UTC day.
SELECT
wallet_address,
count() AS fills,
sum(price * size) AS notional_usd
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt = '2026-09-01'
WHERE coin = 'BTC'
GROUP BY wallet_address
ORDER BY notional_usd DESC
LIMIT 20utc_fill_dt is the raw fill partition key. Put your date bound in PREWHERE; filter coin for market-specific work.
Set the date filter to your study window. LIMIT controls how many rows are returned; the date filter bounds the data scanned.
closed_pnl and fee are already USD Float64 values. Do not divide them by token base units.
Choose the raw table for individual fills, an order rollup for order-level totals, or a derived view for wallet and position research.
hyperliquidScroll the table on a narrow screen to compare history and state →
| Dataset | Research surface | History / state |
|---|---|---|
raw_node_fills_by_block | One row per fill: price, size, side, fees, position before the fill, realized PnL and execution context. | All perpetual markets, with history from 27 July 2025 and real-time refresh. |
agg_fulfilled_order | Fills rolled up by order. Read size, volume and PnL with sum(). | 90-day retention. |
view_perpetual_wallet | Derived wallet aggregates: realized PnL, volume, win counts, active days and ROI quantiles. | A computed view derived from its source tables. |
view_wallet_position | Latest position per wallet and market, with update time. | Latest position state per wallet and market. |
Inspect full schemas and partition keys →
The SQL warehouse contains fills and derived views. To study the book around an event, request the separate order-level archive for your markets and date range.
Connect to ClickHouse and query tables. Use it for trade flow, wallet activity, liquidation context and builder attribution.
MessagePack ABCI checkpoints plus hourly zstd streams for book diffs, order statuses and fills. Archive access is provisioned separately; confirm scope and terms with the team.
Load the newest snapshot at or before the target time, including asset encoding and precision.
Read hourly diff files covering the interval after the checkpoint.
Apply insert, amend and remove events in order, keyed by order ID.
Build price levels, then sample spread, midpoint and depth for the study.
Historical perpetual-market data through SQL.
Free trial up to 24h on request; our team confirms availability and setup. Monthly, prepaid in crypto.
Monthly, prepaid. Crypto-only billing: USDC, SOL, ETH or USDT. Confirm capacity and workload fit before subscribing. Separate archive access and any custom integration need an agreed scope.
Start in the portal. Our team provisions your host, port, username and password for the hyperliquid database.
Share markets, date range and expected queries in your access request.
Our team arranges connection details per customer. Review the dataset and archive scope before connecting.
Set database hyperliquid, test a bounded query and keep credentials out of source code.
The ClickHouse database covers fill activity and derived views. The order-level book archive is a separately provisioned surface with checkpoints and diffs. Agree markets, date coverage and terms with the team.
The 90-day retention applies to agg_fulfilled_order. Raw fill history starts 27 July 2025. Wallet views compute aggregates, while the latest-position view shows the latest state per wallet and market.
A custom REST endpoint around a specific query can be discussed on request. Agree its scope and price separately.
Request a trial through the portal. Our team confirms availability and setup for up to 24 hours.
Table reference lists columns, types and partition keys. Connection Docs cover Python and DBeaver setup.