ScoutQL recipes

Use these when you know what you want to ask and need the shape that asks it. Each recipe is a complete query: paste it into the report editor, change the column and the window, and run it.

They are also the recipes Scout’s own AI assistants are given, and the snippets the editor offers while you type — one list, so the answer you get from the editor and the answer you get from the assistant cannot disagree.

Before you start, open the report editor (Reports → New report in your server’s dashboard) so the live preview can run each one against your own match history. Everything below assumes match_participants, which is one row per tracked player per finished match.

Win rate

A rate is the average of a boolean cast to an integer. DuckDB will not average a BOOLEAN, so the `::INT` is required — and it is what makes the value a percentage rather than a count.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 10
ORDER BY win_rate DESC
RENDER leaderboard

Conditional aggregate with FILTER

`FILTER (WHERE …)` narrows one aggregate without narrowing the query, so wins, losses, and a solo-queue win rate can share a single pass over the same rows.

SELECT COUNT(*) AS games, COUNT(*) FILTER (WHERE win) AS wins, COUNT(*) FILTER (WHERE NOT win) AS losses, AVG(win::INT) FILTER (WHERE queue = 'solo') AS solo_win_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player

Medians and percentiles

`MEDIAN(x)` and `QUANTILE_CONT(x, q)` describe a distribution's shape where an average hides it — a 90th-percentile damage number answers "how big are the big games?".

SELECT MEDIAN(total_damage_dealt_to_champions) AS median_damage, QUANTILE_CONT(total_damage_dealt_to_champions, 0.9) AS p90
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY champion
ORDER BY p90 DESC
LIMIT 10

Distinct counts

`COUNT(DISTINCT x)` counts unique values — champion pool size, distinct queues played. It is not additive, so it cannot be accumulated across buckets.

SELECT COUNT(*) AS games, COUNT(DISTINCT champion_id) AS champions
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
ORDER BY champions DESC

Per-minute rates and KDA

`per_minute(x)` divides a summed counter by minutes played, guarding a zero denominator; `kda()` is takedowns over at-least-one death, so a deathless streak still ranks by takedowns.

SELECT COUNT(*) AS games, per_minute(creep_score) AS cs_per_minute, kda() AS kda
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
ORDER BY cs_per_minute DESC

A rolling time bound

A relative bound is an ordinary WHERE conjunct. Omitting it is legal and means every ingested game, which is rarely the question — state a window unless you mean all history.

SELECT COUNT(*) AS games
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 7 DAY
GROUP BY player
ORDER BY games DESC

A calendar month, in a real time zone

Calendar bounds compare dates, not instants: shift the timestamp into the zone the days are measured in, cast to DATE, and BETWEEN two ISO dates (both ends inclusive).

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE (game_creation_at AT TIME ZONE 'America/Los_Angeles')::DATE BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY player

A weekly trend

Time series bucket with `DATE_TRUNC` in GROUP BY and echo the same expression in SELECT so the bucket has a name to plot and sort by.

SELECT DATE_TRUNC('week', game_creation_at) AS week, COUNT(*) AS games
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY DATE_TRUNC('week', game_creation_at)
ORDER BY week ASC
RENDER line_chart WITH (y = games, smooth = true)

This period against the last

`compare = previous_period` re-runs the same aggregation over the equally long span immediately before the window and overlays it. It needs a stated window and a time bucket to line the two up.

SELECT DATE_TRUNC('week', game_creation_at) AS week, COUNT(*) FILTER (WHERE win) AS wins, COUNT(*) FILTER (WHERE NOT win) AS losses
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY DATE_TRUNC('week', game_creation_at)
ORDER BY week ASC
RENDER stacked_bar WITH (y = (wins, losses), compare = previous_period)

A distribution histogram

Bucket a numeric column with `FLOOR(x / width) * width` and count rows per bucket. The multiplication keeps the bucket's real starting value, so the axis reads in the column's own units.

SELECT FLOOR(game_duration_seconds / 300) * 300 AS bucket, COUNT(*) AS games
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY FLOOR(game_duration_seconds / 300) * 300
ORDER BY bucket ASC
RENDER histogram WITH (x = bucket, y = games)

One player, one champion

`player('…')` resolves a tracked player's accounts at run time, and `champion('…')` folds a display name to its numeric id at compile time (misspellings get a suggestion).

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE player('Bob')
  AND champion_id = champion('Jinx')
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY patch
ORDER BY patch ASC

A five-number summary

Box plots take their five outputs explicitly, in the order min, q1, median, q3, max — the same explicit-SQL bargain as the rest of the language, so nothing is guessed from a column name.

SELECT champion, MIN(kills) AS low, QUANTILE_CONT(kills, 0.25) AS q1, MEDIAN(kills) AS med, QUANTILE_CONT(kills, 0.75) AS q3, MAX(kills) AS high
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY champion
LIMIT 8
RENDER box_plot WITH (y = (low, q1, med, q3, high))

Combining them

The recipes compose. A weekly trend of one player’s solo-queue win rate, with a minimum of five games a week, is the rolling window, the boolean average, the DATE_TRUNC bucket, and a HAVING floor in one query:

SELECT DATE_TRUNC('week', game_creation_at) AS week,
       COUNT(*) AS games,
       AVG(win::INT) AS win_rate
FROM match_participants
WHERE player('Bob')
  AND queue = 'solo'
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 180 DAY
GROUP BY DATE_TRUNC('week', game_creation_at)
HAVING games >= 5
ORDER BY week ASC
RENDER line_chart WITH (y = win_rate, x_axis = 'Week', smooth = true)

If a recipe returns nothing

Work down this list before rewriting the query:

The window is shorter than the activity. A 7-day window on a quiet server is empty and correct. Widen it.
A HAVING floor is too high. HAVING games >= 10 removes every player in a week nobody played ten games.
A queue filter excludes everything. Queue values are listed in Queues and regions, and they are compared exactly.
The source has no rows for those players. Reports read matches Scout ingested after the players were tracked — there is no backfill of history from before that.

ScoutQL — the language itself.
Functions — everything you can compute.
Turn a report into a chart