Skip to content
Outcome
Open the app
All research guides

Research methods · 9 min read

Build a prediction market calibration chart with SQL

Were markets priced near 70% actually right about 70% of the time? This walkthrough gives you SQL for a reliability chart and a Brier score, plus the individual forecasts needed to check the result. It uses one Polymarket trade price per market at a fixed UTC cutoff, so heavily traded markets do not receive extra votes.

1. Pick the snapshot you want to evaluate

Open an Outcome notebook or the SQL editor and use the queries below. Executing queries requires an account and credits. The example freezes forecasts at October 1, 2026, 00:00 UTC and includes eligible markets recorded as settling from October 2 through October 14. Change forecast_cutoff and analysis_cutoff in every query to study another period; keep the rules unchanged when comparing snapshots.

The starting cohort contains markets with a trade in the 24 hours before the forecast cutoff. Each gets its last observed YES price in that window. Markets without a recent observation are outside this cohort. This is an evaluation of actively traded, subsequently resolved markets, not every listed market or a fixed-horizon forecast benchmark.

This example uses Polymarket because the method needs an actual settled_time. Outcome currently leaves that field unavailable for Kalshi; substituting its scheduled close or expiration would change the question. Even a later settlement does not prove the event was unknown at the forecast cutoff: read the rules and remove already-determined events before presenting the result as forecasting skill.

2. Count what the study includes and excludes

Run this first. It keeps only binary markets with a final yes or no result, a valid price, and a recorded settlement at least 24 hours after the forecast cutoff but before the analysis cutoff. Split payouts, unknown results, and missing settlement times are excluded explicitly. The output is cohort_status and markets; save these counts beside your chart.

These are current metadata and result labels evaluated against a fixed time window, not an archived database snapshot as it existed on the analysis date. Later corrections can change a rerun. The 24-hour trade filter limits the scan, but a short result does not guarantee a cheap query.

Audit the complete cohort before scoring
WITH
  toDateTime('2026-10-01 00:00:00', 'UTC') AS forecast_cutoff,
  toDateTime('2026-10-15 00:00:00', 'UTC') AS analysis_cutoff,
  forecasts AS (
    SELECT
      venue,
      ticker,
      argMax(
        tuple(yes_price, created_time, trade_id),
        tuple(created_time, trade_id)
      ) AS last_trade
    FROM trades FINAL
    WHERE venue = 'polymarket'
      AND is_deleted = 0
      AND created_time >= forecast_cutoff - INTERVAL 24 HOUR
      AND created_time < forecast_cutoff
    GROUP BY venue, ticker
  ),
  cohort AS (
    SELECT
      f.venue AS venue,
      f.ticker AS ticker,
      f.last_trade.1 AS forecast,
      f.last_trade.2 AS observed_at,
      f.last_trade.3 AS trade_id,
      m.event_ticker AS event_ticker,
      m.title AS title,
      m.yes_sub_title AS yes_sub_title,
      m.no_sub_title AS no_sub_title,
      m.rules_primary AS rules_primary,
      lower(ifNull(m.result, '')) AS outcome_label,
      m.settled_time AS settled_time,
      multiIf(
        ifNull(m.metadata_found, 0) = 0, 'missing_metadata',
        NOT isFinite(forecast) OR forecast < 0 OR forecast > 1, 'invalid_price',
        ifNull(m.market_type, '') != 'binary', 'not_binary_market',
        m.status != 'settled' OR outcome_label NOT IN ('yes', 'no'), 'no_binary_result',
        isNull(m.settled_time), 'missing_settlement_time',
        m.settled_time < forecast_cutoff + INTERVAL 24 HOUR, 'settled_too_soon',
        m.settled_time >= analysis_cutoff, 'settled_after_window',
        'included'
      ) AS cohort_status
    FROM forecasts AS f
    LEFT JOIN (
      SELECT
        venue, ticker, event_ticker, title, result,
        yes_sub_title, no_sub_title, rules_primary,
        status, market_type, settled_time, 1 AS metadata_found
      FROM market_metadata
      WHERE venue = 'polymarket' AND is_deleted = 0
    ) AS m ON f.venue = m.venue AND f.ticker = m.ticker
  ),
  scored AS (
    SELECT *, toUInt8(outcome_label = 'yes') AS outcome
    FROM cohort
    WHERE cohort_status = 'included'
  )
SELECT cohort_status, count() AS markets
FROM cohort
GROUP BY cohort_status
ORDER BY markets DESC

3. Calculate reliability bins and the Brier score

This query repeats the same cohort definition and returns one row per occupied 10-percentage-point bin. Bins are lower-inclusive and upper-exclusive, except the final 0.9–1.0 bin includes 1.0. No output means no eligible scored markets; it does not mean perfect calibration.

Plot mean_forecast on the horizontal axis and yes_rate on the vertical axis, both from 0 to 1. Add the diagonal y = x, and show n_markets with each point. A point above the diagonal means YES happened more often than these prices implied; a point below means less often. Use the observed mean forecast, rather than the bin midpoint.

Return chart points, sample sizes, and market-weighted Brier scores
WITH
  toDateTime('2026-10-01 00:00:00', 'UTC') AS forecast_cutoff,
  toDateTime('2026-10-15 00:00:00', 'UTC') AS analysis_cutoff,
  forecasts AS (
    SELECT
      venue,
      ticker,
      argMax(
        tuple(yes_price, created_time, trade_id),
        tuple(created_time, trade_id)
      ) AS last_trade
    FROM trades FINAL
    WHERE venue = 'polymarket'
      AND is_deleted = 0
      AND created_time >= forecast_cutoff - INTERVAL 24 HOUR
      AND created_time < forecast_cutoff
    GROUP BY venue, ticker
  ),
  cohort AS (
    SELECT
      f.venue AS venue,
      f.ticker AS ticker,
      f.last_trade.1 AS forecast,
      f.last_trade.2 AS observed_at,
      f.last_trade.3 AS trade_id,
      m.event_ticker AS event_ticker,
      m.title AS title,
      m.yes_sub_title AS yes_sub_title,
      m.no_sub_title AS no_sub_title,
      m.rules_primary AS rules_primary,
      lower(ifNull(m.result, '')) AS outcome_label,
      m.settled_time AS settled_time,
      multiIf(
        ifNull(m.metadata_found, 0) = 0, 'missing_metadata',
        NOT isFinite(forecast) OR forecast < 0 OR forecast > 1, 'invalid_price',
        ifNull(m.market_type, '') != 'binary', 'not_binary_market',
        m.status != 'settled' OR outcome_label NOT IN ('yes', 'no'), 'no_binary_result',
        isNull(m.settled_time), 'missing_settlement_time',
        m.settled_time < forecast_cutoff + INTERVAL 24 HOUR, 'settled_too_soon',
        m.settled_time >= analysis_cutoff, 'settled_after_window',
        'included'
      ) AS cohort_status
    FROM forecasts AS f
    LEFT JOIN (
      SELECT
        venue, ticker, event_ticker, title, result,
        yes_sub_title, no_sub_title, rules_primary,
        status, market_type, settled_time, 1 AS metadata_found
      FROM market_metadata
      WHERE venue = 'polymarket' AND is_deleted = 0
    ) AS m ON f.venue = m.venue AND f.ticker = m.ticker
  ),
  scored AS (
    SELECT *, toUInt8(outcome_label = 'yes') AS outcome
    FROM cohort
    WHERE cohort_status = 'included'
  )
SELECT
  bin_low,
  n_markets,
  mean_forecast,
  yes_rate,
  yes_rate - mean_forecast AS calibration_gap,
  bin_brier,
  sum(total_squared_error) OVER () / sum(n_markets) OVER () AS overall_brier,
  sum(n_markets) OVER () AS scored_markets,
  avg_staleness_hours
FROM (
  SELECT
    least(toUInt8(floor(forecast * 10)), toUInt8(9)) / 10.0 AS bin_low,
    count() AS n_markets,
    avg(forecast) AS mean_forecast,
    avg(outcome) AS yes_rate,
    avg(pow(forecast - outcome, 2)) AS bin_brier,
    sum(pow(forecast - outcome, 2)) AS total_squared_error,
    avg(dateDiff('second', observed_at, forecast_cutoff)) / 3600.0 AS avg_staleness_hours
  FROM scored
  GROUP BY bin_low
)
ORDER BY bin_low

4. Save the reliability chart as an image

Use the CSV export walkthrough below: put the complete query from step 3 in trades.sql, run its export and download steps only, then rename the downloaded file with mv trades.csv calibration.csv. Keep the column headers and use this plotting script instead of that guide's trade-inspection script. Install the dependency with python -m pip install matplotlib, save this as plot_calibration.py, then run python plot_calibration.py. It writes calibration.png with sample sizes and the overall Brier score.

Create calibration.png from the exported query result
import csv
from pathlib import Path
import matplotlib.pyplot as plt

with Path("calibration.csv").open(newline="") as source:
    rows = list(csv.DictReader(source))
if not rows:
    raise SystemExit("No scored markets; inspect the cohort audit first.")
x = [float(row["mean_forecast"]) for row in rows]
y = [float(row["yes_rate"]) for row in rows]
n = [int(row["n_markets"]) for row in rows]
brier = float(rows[0]["overall_brier"])
fig, ax = plt.subplots(figsize=(6, 6))
ax.plot([0, 1], [0, 1], "--", color="gray", label="Perfect calibration")
ax.scatter(x, y, s=55, label="Observed bins", clip_on=False)
for px, py, count in zip(x, y, n):
    ax.annotate(f"n={count}", (px, py),
                xytext=(-5 if px > .85 else 5, -8 if py > .9 else 5),
                ha="right" if px > .85 else "left",
                va="top" if py > .9 else "bottom",
                textcoords="offset points", fontsize=8)
ax.set(xlim=(0, 1), ylim=(0, 1), xlabel="Mean YES forecast",
       ylabel="Observed YES frequency",
       title=f"Polymarket calibration | Brier {brier:.4f}")
ax.set_aspect("equal")
ax.legend(loc="lower right")
fig.savefig("calibration.png", dpi=180, bbox_inches="tight")
plt.close(fig)

5. Inspect the markets behind a surprising point

Before calling a gap a pricing mistake, inspect its constituent markets. In either complete query above, keep the WITH block and replace the final SELECT with the query below. This example opens the 0.6–0.7 bin; change those two bounds to investigate another bin. The output includes outcome labels and primary rules: Outcome's canonical YES corresponds to Polymarket's first outcome, which can be a named option rather than the literal word Yes. Check those fields, event timing, and stale trades. A trade ID breaks equal-timestamp ties reproducibly, not necessarily in execution order.

Several contracts can describe the same election or release. One row per market prevents trade-volume weighting, but it does not make those markets independent. Use event_ticker as a starting point for reviewing related contracts, and document any event-level grouping. Do not turn a small bin into a confident trading rule.

Replace the final SELECT after the shared WITH block
SELECT
  venue, ticker, event_ticker, title,
  yes_sub_title, no_sub_title, rules_primary,
  observed_at, trade_id, forecast, outcome, settled_time,
  pow(forecast - outcome, 2) AS squared_error
FROM scored
WHERE forecast >= 0.6 AND forecast < 0.7
ORDER BY squared_error DESC, ticker

6. Decide what the result supports

overall_brier is the mean squared probability error across markets, repeated on each bin row. Lower is better on the same cohort. A constant 50% forecast scores 0.25 for binary outcomes, providing a simple reference. Brier score measures both calibration and useful discrimination; it is not a pure calibration score or a profitability estimate.

If a gap survives inspection, repeat the analysis at other prespecified cutoffs and on a separate period before using it to adjust forecasts. Keep the date window and freshness rule visible, and compare methods on identical markets. Publish the SQL, exclusion counts, sample sizes, query time, and the market-level export with your notebook so the next reader can verify the finding.