Polymarket query examples

Ready-to-run SQL query patterns for prediction-market analysis on Polymarket. All examples run against the polymarket ClickHouse database. See the Table Reference for the full schema and the Indexer overview for connection details.

Market analysis

Highest-volume markets (24h)

Identifies top-performing markets by trading activity.

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.

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

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

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

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.

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

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

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

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

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

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.

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

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.

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:

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

Need help shaping a query for your use case? Reach out on Telegram: @supanode_tgs.