# Polymarket query examples

> SQL query patterns for Polymarket prediction-market analysis — market volume, price movement, trader and market-maker stats, order-flow, and category trends.

Ready-to-run SQL query patterns for prediction-market analysis on Polymarket. All examples run against the `polymarket` ClickHouse database. See the [Table Reference](https://supanode.xyz/docs/polymarket/tables) for the full schema and the [Indexer overview](https://supanode.xyz/docs/polymarket/indexer) for connection details.

<Note>
The patterns below use field aliases (`market_id`, `event_type`, `taker`, `maker`, `outcome`, `price`, `size`, `category`, `question`) to express common analytics shapes. Map them onto the physical schema in the [Table Reference](https://supanode.xyz/docs/polymarket/tables) when adapting a query to your own pipeline.
</Note>

## Market analysis

### Highest-volume markets (24h)

Identifies top-performing markets by trading activity.

```sql
SELECT
    e.market_id,
    m.question,
    m.category,
    count(*)                  AS fills,
    sum(e.size * e.price)     AS volume
FROM orderbook_events AS e
LEFT JOIN markets AS m ON e.market_id = m.id
WHERE e.event_type = 'FILL'
  AND e.timestamp > now() - INTERVAL 24 HOUR
GROUP BY e.market_id, m.question, m.category
ORDER BY volume DESC
LIMIT 20;
```

### Price-movement detection

Find markets with significant price swings.

```sql
WITH price_changes AS (
    SELECT
        market_id,
        outcome,
        min(price)                  AS low_price,
        max(price)                  AS high_price,
        argMin(price, timestamp)    AS open_price,
        argMax(price, timestamp)    AS close_price
    FROM orderbook_events
    WHERE event_type = 'FILL'
      AND timestamp > now() - INTERVAL 24 HOUR
    GROUP BY market_id, outcome
)
SELECT
    p.market_id,
    m.question,
    p.outcome,
    p.open_price,
    p.close_price,
    p.close_price - p.open_price    AS price_change,
    p.high_price - p.low_price      AS range
FROM price_changes AS p
LEFT JOIN markets AS m ON p.market_id = m.id
WHERE abs(p.close_price - p.open_price) > 0.1
ORDER BY abs(p.close_price - p.open_price) DESC
LIMIT 50;
```

### Markets ending soon

```sql
SELECT
    id,
    question,
    category,
    end_date,
    dateDiff('hour', now(), end_date)   AS hours_remaining,
    volume
FROM markets
WHERE resolved = 0
  AND end_date > now()
  AND end_date < now() + INTERVAL 7 DAY
ORDER BY end_date
LIMIT 50;
```

## Trader analysis

### Most active traders

```sql
SELECT
    taker                       AS wallet,
    count(*)                    AS trades,
    sum(size * price)           AS volume,
    count(DISTINCT market_id)   AS markets,
    countIf(side = 'BUY')       AS buys,
    countIf(side = 'SELL')      AS sells
FROM orderbook_events
WHERE event_type = 'FILL'
  AND timestamp > now() - INTERVAL 7 DAY
GROUP BY wallet
ORDER BY volume DESC
LIMIT 100;
```

### Top market makers

```sql
SELECT
    maker,
    count(*)                    AS orders_filled,
    sum(size * price)           AS volume_provided,
    count(DISTINCT market_id)   AS markets_made,
    avg(size)                   AS avg_order_size
FROM orderbook_events
WHERE event_type = 'FILL'
  AND timestamp > now() - INTERVAL 7 DAY
GROUP BY maker
ORDER BY volume_provided DESC
LIMIT 50;
```

### Trader PnL estimation

Estimate realized PnL for resolved markets.

```sql
WITH trades AS (
    SELECT
        e.taker AS wallet,
        e.market_id,
        e.outcome,
        e.side,
        e.price,
        e.size,
        m.winning_outcome
    FROM orderbook_events AS e
    JOIN markets AS m ON e.market_id = m.id
    WHERE e.event_type = 'FILL'
      AND m.resolved = 1
      AND e.timestamp > now() - INTERVAL 30 DAY
)
SELECT
    wallet,
    count(*) AS trades,
    sum(
        CASE
            WHEN outcome = winning_outcome  AND side = 'BUY'  THEN (1 - price) * size
            WHEN outcome = winning_outcome  AND side = 'SELL' THEN -(1 - price) * size
            WHEN outcome != winning_outcome AND side = 'BUY'  THEN -price * size
            WHEN outcome != winning_outcome AND side = 'SELL' THEN price * size
            ELSE 0
        END
    ) AS estimated_pnl
FROM trades
GROUP BY wallet
ORDER BY estimated_pnl DESC
LIMIT 50;
```

## Order-book analysis

### Order-flow imbalance

```sql
SELECT
    toStartOfMinute(timestamp)  AS minute,
    market_id,
    sumIf(size, side = 'BUY'  AND event_type = 'PLACEMENT')  AS buy_orders,
    sumIf(size, side = 'SELL' AND event_type = 'PLACEMENT')  AS sell_orders,
    sumIf(size, side = 'BUY'  AND event_type = 'PLACEMENT')
        - sumIf(size, side = 'SELL' AND event_type = 'PLACEMENT') AS imbalance
FROM orderbook_events
WHERE market_id = 'your_market_id'
  AND timestamp > now() - INTERVAL 1 HOUR
GROUP BY minute, market_id
ORDER BY minute;
```

### Cancellation rate

```sql
SELECT
    maker,
    countIf(event_type = 'PLACEMENT')       AS placements,
    countIf(event_type = 'CANCELLATION')    AS cancellations,
    countIf(event_type = 'FILL')            AS fills,
    round(
        countIf(event_type = 'CANCELLATION')
        / countIf(event_type = 'PLACEMENT') * 100,
        2
    ) AS cancel_rate_pct
FROM orderbook_events
WHERE timestamp > now() - INTERVAL 24 HOUR
GROUP BY maker
HAVING placements > 100
ORDER BY cancel_rate_pct DESC
LIMIT 50;
```

### Spread analysis

```sql
WITH order_book AS (
    SELECT
        toStartOfMinute(timestamp)      AS minute,
        market_id,
        outcome,
        maxIf(price, side = 'BUY')      AS best_bid,
        minIf(price, side = 'SELL')     AS best_ask
    FROM orderbook_events
    WHERE event_type = 'PLACEMENT'
      AND market_id = 'your_market_id'
      AND timestamp > now() - INTERVAL 1 HOUR
    GROUP BY minute, market_id, outcome
)
SELECT
    minute,
    outcome,
    best_bid,
    best_ask,
    best_ask - best_bid AS spread
FROM order_book
WHERE best_bid > 0
  AND best_ask > 0
ORDER BY minute;
```

## Time-based analysis

### Hourly volume pattern

```sql
SELECT
    toHour(timestamp)       AS hour_of_day,
    count(*)                AS fills,
    sum(size * price)       AS volume
FROM orderbook_events
WHERE event_type = 'FILL'
  AND timestamp > now() - INTERVAL 30 DAY
GROUP BY hour_of_day
ORDER BY hour_of_day;
```

### Daily active markets

```sql
SELECT
    toDate(timestamp)           AS date,
    count(DISTINCT market_id)   AS active_markets,
    count(*)                    AS total_fills,
    sum(size * price)           AS total_volume
FROM orderbook_events
WHERE event_type = 'FILL'
  AND timestamp > now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date;
```

### Event-impact analysis

Track price movement around specific timestamps.

```sql
SELECT
    toStartOfMinute(timestamp)  AS minute,
    outcome,
    avg(price)                  AS avg_price,
    count(*)                    AS fills,
    sum(size)                   AS volume
FROM orderbook_events
WHERE market_id = 'your_market_id'
  AND event_type = 'FILL'
  AND timestamp BETWEEN toDateTime('2024-11-05 00:00:00')
                    AND toDateTime('2024-11-06 00:00:00')
GROUP BY minute, outcome
ORDER BY minute;
```

## Category analysis

### Volume by category

```sql
SELECT
    m.category,
    count(DISTINCT e.market_id) AS markets,
    count(*)                    AS fills,
    sum(e.size * e.price)       AS volume
FROM orderbook_events AS e
JOIN markets AS m ON e.market_id = m.id
WHERE e.event_type = 'FILL'
  AND e.timestamp > now() - INTERVAL 7 DAY
GROUP BY m.category
ORDER BY volume DESC;
```

### Trending categories

Compare recent volume to historical.

```sql
WITH recent AS (
    SELECT
        m.category,
        sum(e.size * e.price) AS volume_7d
    FROM orderbook_events AS e
    JOIN markets AS m ON e.market_id = m.id
    WHERE e.event_type = 'FILL'
      AND e.timestamp > now() - INTERVAL 7 DAY
    GROUP BY m.category
),
historical AS (
    SELECT
        m.category,
        sum(e.size * e.price) / 4 AS avg_weekly_volume
    FROM orderbook_events AS e
    JOIN markets AS m ON e.market_id = m.id
    WHERE e.event_type = 'FILL'
      AND e.timestamp BETWEEN now() - INTERVAL 35 DAY
                          AND now() - INTERVAL 7 DAY
    GROUP BY m.category
)
SELECT
    r.category,
    r.volume_7d,
    h.avg_weekly_volume,
    r.volume_7d / nullIf(h.avg_weekly_volume, 0) AS volume_ratio
FROM recent AS r
LEFT JOIN historical AS h ON r.category = h.category
ORDER BY volume_ratio DESC;
```

## Performance tips

1. **Filter by timestamp first.** Tables use date-based partitioning, so a timestamp predicate prunes whole partitions before scanning.
2. **Join with markets sparingly.** The markets table stays small; filter events first, then join for context.
3. **Use a `market_id` filter.** Constraining to a single market substantially decreases the scan size.

Check the query plan before running heavy scans:

```sql
EXPLAIN
SELECT count(*)
FROM orderbook_events
WHERE timestamp > today();
```

Need help shaping a query for your use case? Reach out on Telegram: [@supanode_tgs](https://t.me/supanode_tgs).