# Hyperliquid query examples

> Practical SQL patterns for Hyperliquid perpetual trading analysis — traders, markets, liquidations, funding, TWAP, and cross-analysis.

Practical SQL patterns for perpetual trading analysis on the [Hyperliquid Indexer](https://supanode.xyz/docs/hyperliquid/indexer). See [/hyperliquid/tables](https://supanode.xyz/docs/hyperliquid/tables) for the full schema reference.

<Note>
The patterns below use table and field aliases (`fills`, `fundings`, `twaps`, `user_fundings`; `user`, `px`, `sz`, `closedPnl`, `time`) to express common analytics shapes. Map them onto the physical schema in the [Table Reference](https://supanode.xyz/docs/hyperliquid/tables) when adapting a query to your own pipeline.
</Note>

## Trader analysis

### Most profitable traders (30 days)

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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.

```sql
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:

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

## Get started

Provision access or start a trial: [@supanode_tgs](https://t.me/supanode_tgs).