Common Queries - Probalytics

Production-ready queries for common prediction market analysis tasks. All queries include platform and date filters for optimal performance.

Market Discovery

Search Markets by Keyword

SELECT
    id,
    platform,
    title,
    status,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
  AND (
    title ILIKE '%bitcoin%'
    OR title ILIKE '%btc%'
  )
ORDER BY created_at DESC
LIMIT 50;

Markets Closing Soon

Find active markets closing in the next 7 days—useful for time-sensitive analysis.

SELECT
    id,
    platform,
    title,
    closes_at,
    dateDiff('hour', now(), closes_at) as hours_until_close
FROM markets
WHERE platform = 'POLYMARKET'
  AND status = 'ACTIVE'
  AND closes_at BETWEEN now() AND now() + INTERVAL 7 DAY
ORDER BY closes_at ASC
LIMIT 100;

Recently Created Markets

SELECT
    id,
    platform,
    title,
    category,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 24 HOUR
ORDER BY created_at DESC
LIMIT 50;

Most Traded Markets (Last 7 Days)

SELECT
    f.market_id,
    m.title,
    m.platform,
    count() as fill_count,
    sum(f.size) as total_size,
    uniq(f.taker_id) as unique_takers
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE f.platform = 'POLYMARKET'
  AND f.timestamp >= now() - INTERVAL 7 DAY
GROUP BY f.market_id, m.title, m.platform
ORDER BY total_size DESC
LIMIT 25;

Price Analysis

Price History for a Market

Get all fills for a specific market to plot price over time. Use price for per-outcome charts or normalized_price for a single Outcome 0 chart.

SELECT
    timestamp,
    outcome.name as outcome,
    price,
    size,
    taker_side
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 30 DAY
ORDER BY timestamp ASC;

OHLC Candles (Hourly)

Build candlestick data for charting. Uses price (the per-outcome execution price) so each outcome gets correct candles.

SELECT
    toStartOfHour(timestamp) as hour,
    outcome.name as outcome,
    argMin(price, timestamp) as open,
    max(price) as high,
    min(price) as low,
    argMax(price, timestamp) as close,
    sum(size) as volume,
    count() as fill_count
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour, outcome
ORDER BY hour ASC, outcome;

OHLC Candles (Daily)

SELECT
    toDate(timestamp) as date,
    outcome.name as outcome,
    argMin(price, timestamp) as open,
    max(price) as high,
    min(price) as low,
    argMax(price, timestamp) as close,
    sum(size) as volume
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 90 DAY
GROUP BY date, outcome
ORDER BY date ASC, outcome;

Volume-Weighted Average Price (VWAP)

SELECT
    outcome.name as outcome,
    sum(price * size) / sum(size) as vwap,
    sum(size) as total_size
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 24 HOUR
GROUP BY outcome;

Latest Price per Outcome

Get the most recent fill price for each outcome in a market.

SELECT
    outcome.name as outcome,
    argMax(price, timestamp) as latest_price,
    max(timestamp) as last_fill_time
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY outcome;

Trading Activity

Recent Fills on a Market

SELECT
    timestamp,
    outcome.name as outcome,
    taker_side,
    price,
    size,
    taker_id,
    maker_id
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
ORDER BY timestamp DESC
LIMIT 100;

Hourly Volume Over Time

SELECT
    toStartOfHour(timestamp) as hour,
    count() as fills,
    sum(size) as total_size,
    uniq(taker_id) as unique_takers
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour ASC;

Large Fills (Whale Watching)

Find fills above a size threshold.

SELECT
    f.timestamp,
    m.title,
    f.outcome.name as outcome,
    f.taker_side,
    f.price,
    f.size,
    f.taker_id
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE f.platform = 'POLYMARKET'
  AND f.timestamp >= now() - INTERVAL 24 HOUR
  AND f.size > 1000  -- adjust threshold
ORDER BY f.size DESC
LIMIT 100;

Buy vs Sell Pressure

SELECT
    toStartOfHour(timestamp) as hour,
    outcome.name as outcome,
    sumIf(size, taker_side = 'BUY') as buy_size,
    sumIf(size, taker_side = 'SELL') as sell_size,
    sumIf(size, taker_side = 'BUY') - sumIf(size, taker_side = 'SELL') as net_flow
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour, outcome
ORDER BY hour ASC;

Cash Flow Analysis

SELECT
    outcome.name as outcome,
    taker_side,
    count() as fill_count,
    sum(taker_cash_flow) as total_taker_cash_flow,
    sum(maker_cash_flow) as total_maker_cash_flow,
    sum(fee) as total_fees
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY outcome, taker_side;

Trader Analysis

Top Takers by Volume

SELECT
    taker_id,
    count() as fill_count,
    sum(size) as total_size,
    uniq(market_id) as markets_traded,
    min(timestamp) as first_fill,
    max(timestamp) as last_fill
FROM fills
WHERE platform = 'POLYMARKET'
  AND timestamp >= now() - INTERVAL 30 DAY
  AND taker_id IS NOT NULL
GROUP BY taker_id
ORDER BY total_size DESC
LIMIT 100;

Trader Activity on a Specific Market

SELECT
    taker_id,
    count() as fills,
    sum(size) as total_size,
    sumIf(size, taker_side = 'BUY') as buy_size,
    sumIf(size, taker_side = 'SELL') as sell_size,
    avg(price) as avg_price
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND taker_id IS NOT NULL
GROUP BY taker_id
ORDER BY total_size DESC
LIMIT 50;

Trader’s Position (Net Contracts per Outcome)

Estimate a trader’s current position by summing buys minus sells.

SELECT
    outcome.name as outcome,
    sumIf(size, taker_side = 'BUY') - sumIf(size, taker_side = 'SELL') as net_contracts,
    sum(taker_cash_flow) as total_cash_flow
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND taker_id = 'trader-id-here'
GROUP BY outcome;

Trader’s Recent Activity Across Markets

SELECT
    f.timestamp,
    m.title,
    f.outcome.name as outcome,
    f.taker_side,
    f.price,
    f.size
FROM fills f
JOIN markets m ON f.market_id = m.id
WHERE f.platform = 'POLYMARKET'
  AND f.taker_id = 'trader-id-here'
  AND f.timestamp >= now() - INTERVAL 7 DAY
ORDER BY f.timestamp DESC
LIMIT 100;

Orderbook Analysis

Current Best Bid/Ask

SELECT
    outcome.name as outcome,
    bids[1].price as best_bid,
    bids[1].size as bid_size,
    asks[1].price as best_ask,
    asks[1].size as ask_size,
    asks[1].price - bids[1].price as spread,
    timestamp
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND length(bids) > 0
  AND length(asks) > 0
ORDER BY timestamp DESC
LIMIT 1 BY outcome.name;

Spread Over Time

SELECT
    toStartOfMinute(timestamp) as minute,
    outcome.name as outcome,
    avg(asks[1].price - bids[1].price) as avg_spread,
    min(asks[1].price - bids[1].price) as min_spread,
    max(asks[1].price - bids[1].price) as max_spread
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 1 HOUR
  AND length(bids) > 0
  AND length(asks) > 0
GROUP BY minute, outcome
ORDER BY minute ASC;

Orderbook at a Specific Time

Get the book state at an exact point in time (e.g., to correlate with a fill event). Uses the most recent snapshot at or before the target time.

SELECT
    outcome.name as outcome,
    bids[1].price as best_bid,
    asks[1].price as best_ask,
    (bids[1].price + asks[1].price) / 2 as mid_price,
    bids,
    asks,
    timestamp
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND outcome.id = 'your-outcome-uuid-here'
  AND timestamp <= '2026-03-20T14:30:00.000Z'
ORDER BY timestamp DESC
LIMIT 1;

Top-N Depth Levels

Extract only the top N price levels without transferring the full book.

SELECT
    outcome.name as outcome,
    arraySlice(bids, 1, 10) as top_bids,
    arraySlice(asks, 1, 10) as top_asks,
    timestamp
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
ORDER BY timestamp DESC
LIMIT 1 BY outcome.name;

Orderbook Depth

Total size available across all levels.

SELECT
    outcome.name as outcome,
    sumArray(arrayMap(b -> b.2, bids)) as total_bid_size,
    sumArray(arrayMap(a -> a.2, asks)) as total_ask_size,
    timestamp
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 1 DAY
ORDER BY timestamp DESC
LIMIT 1 BY outcome.name;

Tick-Level Reconstruction

Reconstruct millisecond-resolution orderbook data from sparse snapshots using ClickHouse WITH FILL and INTERPOLATE. Each gap is filled with the last known book state (LOCF).

SELECT timestamp, bids, asks
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
  AND outcome.id = 'your-outcome-uuid-here'
ORDER BY timestamp ASC
WITH FILL
    FROM toDateTime64('2026-03-20T14:00:00.000', 3)
    TO   toDateTime64('2026-03-20T14:01:00.000', 3)
    STEP toIntervalMillisecond(1)
INTERPOLATE (bids, asks);

Tick-level reconstruction generates one row per millisecond. Keep the time range small — 1 minute produces 60,000 rows. For longer ranges, aggregate to a coarser resolution instead.

Market Resolution

Recently Resolved Markets

SELECT
    id,
    platform,
    title,
    resolution_type,
    resolution_resolved_at,
    resolution_winning_outcome_id
FROM markets
WHERE platform = 'POLYMARKET'
  AND status = 'RESOLVED'
  AND resolution_resolved_at >= now() - INTERVAL 7 DAY
ORDER BY resolution_resolved_at DESC
LIMIT 50;

Resolution with Winning Outcome Name

SELECT
    m.id,
    m.title,
    m.resolution_resolved_at,
    m.resolution_type,
    winning.name as winning_outcome
FROM markets m
ARRAY JOIN m.outcomes as winning
WHERE m.platform = 'POLYMARKET'
  AND m.status = 'RESOLVED'
  AND m.resolution_resolved_at >= now() - INTERVAL 30 DAY
  AND winning.id = m.resolution_winning_outcome_id
ORDER BY m.resolution_resolved_at DESC
LIMIT 50;

Aggregated Statistics

Daily Platform Stats

SELECT
    toDate(timestamp) as date,
    platform,
    count() as fills,
    sum(size) as total_size,
    uniq(taker_id) as unique_takers,
    uniq(market_id) as active_markets
FROM fills
WHERE timestamp >= now() - INTERVAL 30 DAY
GROUP BY date, platform
ORDER BY date DESC, platform;

Markets by Category

SELECT
    category,
    count() as market_count,
    countIf(status = 'ACTIVE') as active_markets,
    countIf(status = 'RESOLVED') as resolved_markets
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
GROUP BY category
ORDER BY market_count DESC;

Outcome Count Distribution

How many markets have 2 outcomes vs 3+ outcomes?

SELECT
    length(outcomes) as outcome_count,
    count() as market_count
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 90 DAY
GROUP BY outcome_count
ORDER BY outcome_count;

Advanced Patterns

Price Movement Detection

Find markets where price moved significantly in the last hour.

WITH recent_prices AS (
    SELECT
        market_id,
        outcome.name as outcome,
        argMin(price, timestamp) as price_1h_ago,
        argMax(price, timestamp) as current_price
    FROM fills
    WHERE platform = 'POLYMARKET'
      AND timestamp >= now() - INTERVAL 1 HOUR
    GROUP BY market_id, outcome
)
SELECT
    m.title,
    r.outcome,
    r.price_1h_ago,
    r.current_price,
    r.current_price - r.price_1h_ago as price_change,
    (r.current_price - r.price_1h_ago) / r.price_1h_ago * 100 as pct_change
FROM recent_prices r
JOIN markets m ON r.market_id = m.id
WHERE abs(r.current_price - r.price_1h_ago) > 0.05  -- 5+ point move
ORDER BY abs(pct_change) DESC
LIMIT 25;

Markets with Unusual Volume

Find markets trading above their 7-day average.

WITH volume_stats AS (
    SELECT
        market_id,
        sum(size) / 7 as avg_daily_size
    FROM fills
    WHERE platform = 'POLYMARKET'
      AND timestamp BETWEEN now() - INTERVAL 8 DAY AND now() - INTERVAL 1 DAY
    GROUP BY market_id
),
today_volume AS (
    SELECT
        market_id,
        sum(size) as today_size
    FROM fills
    WHERE platform = 'POLYMARKET'
      AND timestamp >= now() - INTERVAL 1 DAY
    GROUP BY market_id
)
SELECT
    m.title,
    t.today_size,
    v.avg_daily_size,
    t.today_size / v.avg_daily_size as volume_ratio
FROM today_volume t
JOIN volume_stats v ON t.market_id = v.market_id
JOIN markets m ON t.market_id = m.id
WHERE v.avg_daily_size > 100  -- filter low-volume markets
ORDER BY volume_ratio DESC
LIMIT 25;

Spread Between Outcomes

For binary markets, check if outcome prices sum to ~1. A large deviation indicates an arbitrage opportunity or stale pricing.

SELECT
    m.title,
    sumIf(latest_price, outcome_index = 0) as yes_price,
    sumIf(latest_price, outcome_index = 1) as no_price,
    sumIf(latest_price, outcome_index = 0) + sumIf(latest_price, outcome_index = 1) as total,
    abs(1 - (sumIf(latest_price, outcome_index = 0) + sumIf(latest_price, outcome_index = 1))) as spread
FROM (
    SELECT
        market_id,
        outcome.index as outcome_index,
        argMax(price, timestamp) as latest_price
    FROM fills
    WHERE platform = 'POLYMARKET'
      AND timestamp >= now() - INTERVAL 1 DAY
    GROUP BY market_id, outcome_index
) t
JOIN markets m ON t.market_id = m.id
WHERE m.market_type = 'BINARY'
GROUP BY m.id, m.title
HAVING spread > 0.02  -- markets with >2% spread
ORDER BY spread DESC
LIMIT 25;

Time-Weighted Average Price (TWAP)

Weight prices by time between fills, not volume.

WITH fill_intervals AS (
    SELECT
        timestamp,
        price,
        leadInFrame(timestamp) OVER (
            ORDER BY timestamp
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) as next_fill_time,
        dateDiff('second', timestamp,
            leadInFrame(timestamp) OVER (
                ORDER BY timestamp
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
            )
        ) as seconds_until_next
    FROM fills
    WHERE platform = 'POLYMARKET'
      AND market_id = 'your-market-uuid-here'
      AND outcome.name = 'Yes'
      AND timestamp >= now() - INTERVAL 24 HOUR
)
SELECT
    sum(price * seconds_until_next) / sum(seconds_until_next) as twap
FROM fill_intervals
WHERE seconds_until_next > 0;

Exporting Data

Export to CSV Format

Add FORMAT CSV or FORMAT CSVWithNames to any query:

SELECT
    timestamp,
    normalized_price,
    size,
    taker_side
FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 30 DAY
ORDER BY timestamp
FORMAT CSVWithNames;

Export to JSON

SELECT
    id,
    title,
    status,
    created_at
FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= now() - INTERVAL 7 DAY
FORMAT JSONEachRow;

When using programmatic clients, the format is usually handled by the client library. These FORMAT clauses are useful for CLI exports or direct HTTP queries.

Documentation Index

​Market Discovery

​Search Markets by Keyword

​Markets Closing Soon

​Recently Created Markets

​Most Traded Markets (Last 7 Days)

​Price Analysis

​Price History for a Market

​OHLC Candles (Hourly)

​OHLC Candles (Daily)

​Volume-Weighted Average Price (VWAP)

​Latest Price per Outcome

​Trading Activity

​Recent Fills on a Market

​Hourly Volume Over Time

​Large Fills (Whale Watching)

​Buy vs Sell Pressure

​Cash Flow Analysis

​Trader Analysis

​Top Takers by Volume

​Trader Activity on a Specific Market

​Trader’s Position (Net Contracts per Outcome)

​Trader’s Recent Activity Across Markets

​Orderbook Analysis

​Current Best Bid/Ask

​Spread Over Time

​Orderbook at a Specific Time

​Top-N Depth Levels

​Orderbook Depth

​Tick-Level Reconstruction

​Market Resolution

​Recently Resolved Markets

​Resolution with Winning Outcome Name

​Aggregated Statistics

​Daily Platform Stats

​Markets by Category

​Outcome Count Distribution

​Advanced Patterns

​Price Movement Detection

​Markets with Unusual Volume

​Spread Between Outcomes

​Time-Weighted Average Price (TWAP)

​Exporting Data

​Export to CSV Format

​Export to JSON

Market Discovery

Search Markets by Keyword

Markets Closing Soon

Recently Created Markets

Most Traded Markets (Last 7 Days)

Price Analysis

Price History for a Market

OHLC Candles (Hourly)

OHLC Candles (Daily)

Volume-Weighted Average Price (VWAP)

Latest Price per Outcome

Trading Activity

Recent Fills on a Market

Hourly Volume Over Time

Large Fills (Whale Watching)

Buy vs Sell Pressure

Cash Flow Analysis

Trader Analysis

Top Takers by Volume

Trader Activity on a Specific Market

Trader’s Position (Net Contracts per Outcome)

Trader’s Recent Activity Across Markets

Orderbook Analysis

Current Best Bid/Ask

Spread Over Time

Orderbook at a Specific Time

Top-N Depth Levels

Orderbook Depth

Tick-Level Reconstruction

Market Resolution

Recently Resolved Markets

Resolution with Winning Outcome Name

Aggregated Statistics

Daily Platform Stats

Markets by Category

Outcome Count Distribution

Advanced Patterns

Price Movement Detection

Markets with Unusual Volume

Spread Between Outcomes

Time-Weighted Average Price (TWAP)

Exporting Data

Export to CSV Format

Export to JSON