Common Queries - Probalytics

Starting-point queries for common prediction market analysis tasks. Add the market, product-type, and time constraints required by your analysis before using them in production.

price is the execution price for the traded outcome. For binary markets, normalized_price expresses the trade relative to outcome index 0 and is normally between 0 and 1. For perpetual markets, normalized_price is the instrument price and is not a probability. Join fills to markets and inspect market_type before combining products.

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;

Stored Gross Amounts and Fees

The current taker_cash_flow and maker_cash_flow values are stored gross/notional amounts, not signed directional cash flows. This query summarizes the stored values; it does not calculate P&L. A zero fee can mean fee data was unavailable.

SELECT
    outcome.name as outcome,
    taker_side,
    count() as fill_count,
    sum(taker_cash_flow) as stored_taker_gross,
    sum(maker_cash_flow) as stored_maker_gross,
    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;

Taker-Side Net Contract Flow per Outcome

Estimate a trader’s taker-side net contract flow by summing buys minus sells.

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

This estimates only the trader’s taker-side contract flow. It does not include fills where the same trader appears as maker, venue-specific settlement, fees, or signed cash P&L.

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.id as outcome_id,
    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,
    continuity,
    path_index
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND length(bids) > 0
  AND length(asks) > 0
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 1 BY outcome.id;

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 state = 'VERIFIED'
  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,
    continuity,
    path_index
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND outcome.id = 'your-outcome-uuid-here'
  AND timestamp <= '2026-03-20T14:30:00.000Z'
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 1;

Top-N Depth Levels

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

SELECT
    outcome.id as outcome_id,
    outcome.name as outcome,
    arraySlice(bids, 1, 10) as top_bids,
    arraySlice(asks, 1, 10) as top_asks,
    timestamp,
    continuity,
    path_index
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 1 BY outcome.id;

Orderbook Depth

Total size available across all levels.

SELECT
    outcome.id as outcome_id,
    outcome.name as outcome,
    arraySum(arrayMap(b -> b.2, bids)) as total_bid_size,
    arraySum(arrayMap(a -> a.2, asks)) as total_ask_size,
    timestamp,
    continuity,
    path_index
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND timestamp >= now() - INTERVAL 1 DAY
  AND state = 'VERIFIED'
ORDER BY timestamp DESC, path_index DESC, indexed_at DESC
LIMIT 1 BY outcome.id;

Tick-Level Reconstruction

Before building a millisecond sampling grid, check whether the window contains any fresh starting points:

SELECT timestamp, path_index, state, continuity
FROM orderbook_snapshots FINAL
WHERE market_id = 'your-market-uuid-here'
  AND outcome.id = 'your-outcome-uuid-here'
  AND timestamp >= toDateTime64('2026-03-20T14:00:00.000000000', 9)
  AND timestamp <  toDateTime64('2026-03-20T14:01:00.000000000', 9)
  AND continuity = 'RESET'
ORDER BY timestamp, path_index;

If the query returns no RESET rows, collapse multiple verified snapshots at the same exchange timestamp to the last path state and then forward-fill:

WITH
    toDateTime64('2026-03-20T14:00:00.000000000', 9) AS range_start,
    toDateTime64('2026-03-20T14:01:00.000000000', 9) AS range_end
SELECT timestamp, bids, asks
FROM (
    SELECT
        timestamp,
        argMax(bids, tuple(path_index, indexed_at)) AS bids,
        argMax(asks, tuple(path_index, indexed_at)) AS asks
    FROM orderbook_snapshots FINAL
    WHERE market_id = 'your-market-uuid-here'
      AND outcome.id = 'your-outcome-uuid-here'
      AND state = 'VERIFIED'
      AND timestamp >= range_start
      AND timestamp < range_end
    GROUP BY timestamp
)
ORDER BY timestamp ASC
WITH FILL
    FROM range_start
    TO range_end
    STEP toIntervalMillisecond(1)
INTERPOLATE (bids, asks);

Forward-filled rows are synthetic sampling points, not exchange events. Do not use them for event counts, feed-quality measurements, or reset detection. Never fill across RESET. One minute at millisecond resolution produces 60,000 rows, so use a coarser grid for longer ranges.

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;

Latest Fill Price Divergence Between Outcomes

For binary markets, compare the most recent execution price for each outcome. The executions can occur at different times and are not executable bid/ask quotes, so a deviation indicates asynchronous or stale prints—not a confirmed arbitrage opportunity.

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 distance_from_one
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 distance_from_one > 0.02
ORDER BY distance_from_one 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.

​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

​Stored Gross Amounts and Fees

​Trader Analysis

​Top Takers by Volume

​Trader Activity on a Specific Market

​Taker-Side Net Contract Flow 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

​Latest Fill Price Divergence 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

Stored Gross Amounts and Fees

Trader Analysis

Top Takers by Volume

Trader Activity on a Specific Market

Taker-Side Net Contract Flow 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

Latest Fill Price Divergence Between Outcomes

Time-Weighted Average Price (TWAP)

Exporting Data

Export to CSV Format

Export to JSON