Hyperliquid query examples

Practical SQL patterns for perpetual trading analysis on the Hyperliquid Indexer. See /hyperliquid/tables for the full schema reference.

Trader analysis

Most profitable traders (30 days)

SELECT
    user,
    count(*)             AS trades,
    sum(closedPnl) / 1e6 AS realized_pnl,
    avg(closedPnl) / 1e6 AS avg_pnl_per_trade,
    countIf(closedPnl > 0) AS wins,
    countIf(closedPnl < 0) AS losses
FROM fills
WHERE time > now() - INTERVAL 30 DAY
  AND closedPnl != 0
GROUP BY user
HAVING trades > 50
ORDER BY realized_pnl DESC
LIMIT 100

Trading volume by wallet

SELECT
    user,
    count(*)              AS trades,
    sum(sz * px)          AS notional_volume,
    count(DISTINCT coin)  AS markets_traded,
    count(DISTINCT toDate(time)) AS active_days
FROM fills
WHERE time > now() - INTERVAL 7 DAY
GROUP BY user
ORDER BY notional_volume DESC
LIMIT 50

Win rate analysis

SELECT
    user,
    count(*)               AS closing_trades,
    countIf(closedPnl > 0) AS profitable,
    countIf(closedPnl < 0) AS unprofitable,
    round(countIf(closedPnl > 0) / count(*) * 100, 2) AS win_rate_pct,
    sum(closedPnl) / 1e6   AS total_pnl
FROM fills
WHERE time > now() - INTERVAL 30 DAY
  AND closedPnl != 0
GROUP BY user
HAVING closing_trades > 100
ORDER BY win_rate_pct DESC
LIMIT 50

Market analysis

Volume by market

SELECT
    coin,
    count(*)     AS trades,
    sum(sz * px) AS notional_volume,
    uniq(user)   AS unique_traders,
    avg(sz * px) AS avg_trade_size
FROM fills
WHERE time > now() - INTERVAL 24 HOUR
GROUP BY coin
ORDER BY notional_volume DESC

Hourly volume trend

SELECT
    toStartOfHour(time) AS hour,
    coin,
    count(*)            AS trades,
    sum(sz * px)        AS volume
FROM fills
WHERE time > now() - INTERVAL 7 DAY
  AND coin = 'BTC'
GROUP BY hour, coin
ORDER BY hour

Long vs short bias

SELECT
    toStartOfHour(time) AS hour,
    coin,
    sumIf(sz, side = 'B') AS buy_volume,
    sumIf(sz, side = 'A') AS sell_volume,
    sumIf(sz, side = 'B') - sumIf(sz, side = 'A') AS net_bias
FROM fills
WHERE time > now() - INTERVAL 24 HOUR
  AND coin = 'ETH'
GROUP BY hour, coin
ORDER BY hour

Liquidation analysis

Recent liquidations

SELECT
    time,
    user,
    coin,
    side,
    px AS liquidation_price,
    sz AS size,
    closedPnl / 1e6 AS pnl
FROM fills
WHERE liquidation = 1
  AND time > now() - INTERVAL 24 HOUR
ORDER BY time DESC
LIMIT 100

Liquidation volume by market

SELECT
    coin,
    count(*)             AS liquidations,
    sum(sz * px)         AS liquidated_notional,
    sum(closedPnl) / 1e6 AS total_pnl_lost
FROM fills
WHERE liquidation = 1
  AND time > now() - INTERVAL 7 DAY
GROUP BY coin
ORDER BY liquidated_notional DESC

Wallets with multiple liquidations

SELECT
    user,
    count(*)     AS liquidation_count,
    sum(sz * px) AS total_liquidated,
    arrayDistinct(groupArray(coin)) AS markets
FROM fills
WHERE liquidation = 1
  AND time > now() - INTERVAL 30 DAY
GROUP BY user
HAVING liquidation_count > 3
ORDER BY liquidation_count DESC
LIMIT 50

Funding analysis

Current funding rates

SELECT
    coin,
    argMax(fundingRate, time)              AS latest_funding,
    argMax(fundingRate, time) * 24 * 365   AS annualized
FROM fundings
WHERE time > now() - INTERVAL 1 HOUR
GROUP BY coin
ORDER BY abs(latest_funding) DESC

Funding rate history

SELECT
    toStartOfHour(time) AS hour,
    coin,
    avg(fundingRate)             AS avg_funding,
    avg(fundingRate) * 24 * 365  AS annualized
FROM fundings
WHERE time > now() - INTERVAL 7 DAY
  AND coin IN ('BTC', 'ETH', 'SOL')
GROUP BY hour, coin
ORDER BY hour, coin

Top funding earners

SELECT
    user,
    sum(usdc) AS total_funding_pnl,
    count(*)  AS funding_events,
    avg(usdc) AS avg_funding_payment
FROM user_fundings
WHERE time > now() - INTERVAL 30 DAY
GROUP BY user
ORDER BY total_funding_pnl DESC
LIMIT 50

TWAP analysis

Active TWAP orders

SELECT
    twapId,
    user,
    coin,
    side,
    min(time) AS started,
    max(time) AS last_fill,
    count(*)  AS fills,
    sum(sz)   AS total_filled,
    sum(sz * px) / sum(sz) AS avg_price
FROM twaps
WHERE time > now() - INTERVAL 24 HOUR
GROUP BY twapId, user, coin, side
ORDER BY started DESC
LIMIT 50

TWAP execution quality

WITH twap_summary AS (
    SELECT
        twapId,
        user,
        coin,
        sum(sz * px) / sum(sz) AS twap_avg_price,
        sum(sz)                AS total_size,
        dateDiff('minute', min(time), max(time)) AS duration_minutes
    FROM twaps
    WHERE time > now() - INTERVAL 7 DAY
    GROUP BY twapId, user, coin
    HAVING count(*) > 5
)
SELECT
    coin,
    count(*)              AS twap_orders,
    avg(total_size)       AS avg_order_size,
    avg(duration_minutes) AS avg_duration_min
FROM twap_summary
GROUP BY coin
ORDER BY twap_orders DESC

Cross-analysis

Correlation: large fills and liquidations

SELECT
    toStartOfMinute(time) AS minute,
    coin,
    sumIf(sz * px, liquidation = 0) AS regular_volume,
    sumIf(sz * px, liquidation = 1) AS liquidation_volume,
    countIf(liquidation = 1)        AS liquidation_count
FROM fills
WHERE time > now() - INTERVAL 1 HOUR
  AND coin = 'BTC'
GROUP BY minute, coin
HAVING liquidation_count > 0
ORDER BY minute

Smart wallet tracker

Track wallets with high win rates and significant volume.

WITH wallet_stats AS (
    SELECT
        user,
        count(*)     AS trades,
        sum(sz * px) AS volume,
        sum(closedPnl) / 1e6 AS pnl,
        countIf(closedPnl > 0) / countIf(closedPnl != 0) AS win_rate
    FROM fills
    WHERE time > now() - INTERVAL 30 DAY
    GROUP BY user
    HAVING trades > 100
       AND countIf(closedPnl != 0) > 50
)
SELECT *
FROM wallet_stats
WHERE win_rate > 0.55
  AND pnl > 10000
ORDER BY pnl DESC
LIMIT 50

Performance tips

• Always filter by time — tables are partitioned by date.
• Use a coin filter — narrows the scan significantly.
• Aggregate first — reduce data before complex operations.

Check a query plan before running heavy scans:

EXPLAIN SELECT count(*) FROM fills WHERE time > today()

Get started

Provision access or start a trial: @supanode_tgs.