> ## Documentation Index
> Fetch the complete documentation index at: https://docs.probalytics.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Common Queries

> Copy-paste SQL query templates for prediction market analysis

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

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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)

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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)

```sql theme={null}
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)

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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).

```sql theme={null}
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);
```

<Warning>
  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.
</Warning>

***

## Market Resolution

### Recently Resolved Markets

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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?

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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:

```sql theme={null}
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

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

<Note>
  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.
</Note>