Skip to content
Outcome
Open the app
All research guides

SQL recipes · 7 min read

Build prediction market candlesticks with SQL

Build a chart-ready table of 15-minute candles for one prediction market. The query below returns open, high, low, and close prices, traded quantity, and a quantity-weighted price. Empty intervals remain visible without inventing a price. Run the queries in Outcome's SQL playground or through the SQL API with an account and credits.

1. Find the market and check its outcome labels

Change bitcoin to a word in the market's title, then copy the venue and ticker from the result you want. The same topic can have several expiration dates and different resolution rules. Keep the venue and ticker together throughout the analysis.

Prices are stored on a 0–1 scale: 0.62 is 62 cents per contract or token. For Polymarket, the canonical YES column represents the first outcome slot, which may be a team name or Up rather than a literal Yes. Read yes_sub_title and no_sub_title before labeling the chart. A Polymarket NO-token trade contributes its complementary first-outcome price to this series.

Find a market and its outcome labels
SELECT
  venue, ticker, title,
  yes_sub_title, no_sub_title,
  open_time, close_time
FROM markets FINAL
WHERE is_deleted = 0
  AND venue IN ('kalshi', 'polymarket')
  AND positionCaseInsensitive(title, 'bitcoin') > 0
ORDER BY volume_24h DESC
LIMIT 20

2. Copy the OHLCV query

Replace YOUR_VENUE, YOUR_TICKER, and both UTC dates. This example covers one complete day, with an inclusive start and exclusive end. Start with a short window containing known trades; wider windows scan more data. The 15-minute boundaries and timestamps use UTC.

FINAL collapses stored row versions before aggregation, and is_deleted removes deleted records. The price and positive-quantity filters select usable observations. argMin and argMax choose the opening and closing price by timestamp, then trade ID. The ID makes ties consistent; it cannot recover the true execution order of trades sharing a timestamp.

15-minute first-outcome candles, including empty intervals
WITH
  toDateTime('2026-10-01 00:00:00', 'UTC') AS start_utc,
  toDateTime('2026-10-02 00:00:00', 'UTC') AS end_utc
SELECT
  toStartOfInterval(created_time, INTERVAL 15 MINUTE, 'UTC') AS bucket_utc,
  toNullable(argMin(yes_price, tuple(created_time, trade_id))) AS open_yes,
  toNullable(max(yes_price)) AS high_yes,
  toNullable(min(yes_price)) AS low_yes,
  toNullable(argMax(yes_price, tuple(created_time, trade_id))) AS close_yes,
  sum(count) AS traded_quantity,
  count() AS observed_trades,
  sum(yes_price * count) / nullIf(sum(count), 0) AS weighted_yes_price
FROM trades FINAL
WHERE venue = 'YOUR_VENUE'
  AND ticker = 'YOUR_TICKER'
  AND is_deleted = 0
  AND count > 0
  AND yes_price BETWEEN 0 AND 1
  AND created_time >= start_utc
  AND created_time < end_utc
GROUP BY bucket_utc
ORDER BY bucket_utc
WITH FILL FROM start_utc TO end_utc STEP INTERVAL 15 MINUTE

3. Map the output to a price and volume chart

Use bucket_utc as the time axis and open_yes, high_yes, low_yes, and close_yes as the candle fields. Keep a fixed 0–1 price axis, or multiply all four fields by 100 for a cents axis. For a simpler line chart, plot close_yes and weighted_yes_price together to see whether the closing trade was representative of the interval.

Plot traded_quantity in a separate volume panel. It is the sum of Kalshi contracts or Polymarket outcome shares, not dollars spent. observed_trades counts stored trade records, so one large fill and many small fills can produce similar quantity with very different record counts. weighted_yes_price weights the canonical first-outcome price by quantity; it is not the average cash price paid for a mixture of YES and NO tokens.

WITH FILL supplies missing intervals. Nullable price columns stay null while observed_trades and traded_quantity become zero. These zeros mean no qualifying observations were returned, not proof that the venue had no trading. Keep a break in the price chart until you have checked the market's operating dates and data coverage. A carried-forward close is an estimated display value, not an observed trade.

4. Change the interval without changing the meaning

For hourly candles, change both occurrences of INTERVAL 15 MINUTE to INTERVAL 1 HOUR and keep the start and end on hour boundaries. Changing only the grouping or only the fill step creates misleading gaps. If you choose a partial first or last interval, label it as partial rather than comparing its volume with complete bars.

A candle summarizes executed observations, so its high and low are not bid/ask quotes and its close is not a guaranteed executable price. Polymarket's canonical prices are rounded to three decimal places; avoid inferring smaller price moves. Save the SQL, selected market labels, observation window, and query time with the chart so it can be refreshed and interpreted later.