ScoutQL

ScoutQL is the language reports and Explore are written in. It is a bounded subset of DuckDB SQL over Scout’s match data, plus a trailing RENDER clause and four Scout-specific calls (player, champion, kda, per_minute).

Where ScoutQL overlaps SQL it means exactly what DuckDB means. There are no conveniences layered on top: AVG(win) is an error in DuckDB and an error here, aggregates are always written out, and a name in WITH (...) has to be a name the SELECT produced.

Clause order

SELECT   <expression> [AS <name>] [, …]     -- 1 to 20 outputs
FROM     <source>
[WHERE   <predicate>]
[GROUP BY ALL | <expression> [, …]]         -- up to 3
[HAVING  <predicate>]
[ORDER BY <key> [ASC | DESC] [, …]]         -- up to 3
[LIMIT   <n>]
[RENDER  <kind> [WITH (<option> = <value>, …)]]

SELECT and FROM are the only required clauses. A query with no GROUP BY returns one grand-total row; a query with no RENDER renders as a table.

Keywords are case-insensitive. String values are not — they are compared exactly, as DuckDB compares them.

Comments

-- comments run to the end of the line and may appear anywhere.

-- Ranked solo activity, last four weeks.
SELECT COUNT(*) AS games
FROM match_participants
WHERE queue = 'solo' -- ARAM and Arena excluded
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 28 DAY
GROUP BY player
ORDER BY games DESC

Outputs are named, and the name is the handle

Every computed output takes an AS name. That name is what HAVING, ORDER BY, and WITH (...) refer to, what a table column is headed with, and what a chart series is labelled with.

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 bar_chart WITH (y = win_rate)

WHERE filters rows; HAVING filters groups

WHERE is a full boolean expression over the source’s columns — AND, OR, NOT, and parentheses — evaluated before rows are collapsed. HAVING is a boolean expression over the aggregates, evaluated after, and may name the aliases the SELECT defined.

An aggregate in WHERE is rejected rather than reinterpreted, because a minimum-games floor written there would silently filter individual games instead of players.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE (queue = 'solo' OR queue = 'flex')
  AND NOT early_surrendered
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 90 DAY
GROUP BY player
HAVING games >= 20 AND win_rate > 0.5
ORDER BY win_rate DESC

Time bounds are ordinary WHERE conditions

There is no time clause. A window is a comparison against the source’s own timestamp column, joined into WHERE with AND like any other condition. The compiler recognizes the shapes below and executes them as the query’s window; anything else is applied as a plain filter.

Relative — a rolling window ending now:

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

Calendar — whole days, in a named zone, both ends inclusive:

SELECT COUNT(*) AS games
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 calendar bound compares dates, not instants, so the zone has to be stated before the cast: “January” starts at a different moment in Los Angeles than in Berlin, and without AT TIME ZONE the days are UTC days.

Each source names the column its window is measured on — see Sources and columns. The rank snapshot sources have no timestamp column at all: a snapshot is current by construction, so there is nothing to bound.

Omitting the bound

A query with no time condition is legal and covers every match Scout has ingested. The editor flags it as a warning (time-window-unbounded) without refusing it.

Both halves of that are deliberate. Refusing would make “how many games have we ever played?” unaskable, and the query is measured — the whole lake is fast to scan. But an unbounded answer read as though it covered the last month is indistinguishable from a correct one, so the warning exists to make the choice visible rather than accidental.

Casts and strings

Strings are single-quoted, and a literal quote is doubled: 'Kai''Sa'. Double quotes are an error, because DuckDB reads them as identifiers and a query that meant a value would silently ask for a column.

:: casts, and it is required wherever DuckDB requires it — most often win::INT, since a boolean has no average until it is a number.

Keywords

Keyword	Meaning
SELECT	Opens the list of outputs.
FROM	Names the source to read.
WHERE	Filters rows before aggregation.
GROUP	With BY, opens the grouping list.
BY	Follows GROUP or ORDER.
HAVING	Filters groups after aggregation.
ORDER	With BY, opens the sort keys.
LIMIT	Caps the number of rows returned.
RENDER	Chooses how the result is displayed.
WITH	Opens the render option list.
AS	Names an output.
AND	Boolean conjunction.
OR	Boolean disjunction.
NOT	Boolean negation.
IN	Membership in a parenthesised list.
IS	Used as IS NULL / IS NOT NULL.
NULL	The unknown value.
BETWEEN	Inclusive range test.
LIKE	Case-sensitive pattern match (% and _).
ILIKE	Case-insensitive pattern match.
ASC	Ascending sort.
DESC	Descending sort.
DISTINCT	Counts distinct values: COUNT(DISTINCT x).
FILTER	Narrows one aggregate: FILTER (WHERE …).
INTERVAL	A duration: INTERVAL 30 DAY.
CAST	CAST(x AS INT), the long form of ::.
AT	Opens AT TIME ZONE.
TIME	Part of AT TIME ZONE.
ZONE	Part of AT TIME ZONE.
ALL	GROUP BY ALL, or mentions = all.
TRUE	Boolean literal.
FALSE	Boolean literal.
CURRENT_TIMESTAMP	Now, as a timestamp.
CURRENT_DATE	Today, as a date.
CASE	Reserved and rejected — use FILTER (WHERE …) instead.

Function names, interval units (DAY, WEEK, MONTH, YEAR), DATE_TRUNC parts, render kinds, and render option names are not keywords. They are ordinary identifiers resolved by meaning, which is why a column called day or an output called sort causes no trouble.

Limits

Limit	Value
Query text length	16000 characters
Outputs per SELECT	1–20
Grouping expressions	0–3
ORDER BY keys	1–3
Default row limit	10
Maximum rows returned	25
Chart series	1–8

The maximum row count is a report setting, not query syntax: a LIMIT larger than the maximum is capped when the report runs.

Vocabulary

Each part of the language has its own page:

• Sources and columns — what FROM, GROUP BY, and every expression may name.
• Functions — what you can compute.
• Filters — what WHERE and HAVING accept.
• Render kinds and options — what RENDER and WITH accept.

Presets

These are the ready-made queries in the report editor. Each one compiles through the same analyzer as anything you type.

Most games played

Find the most active players over the last 30 days.

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
ORDER BY games DESC
LIMIT 10
RENDER leaderboard

Best win rate (ranked solo, min 10 games)

Rank players by solo queue win rate with a games floor.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE queue IN ('solo')
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 10
ORDER BY win_rate DESC
RENDER bar_chart WITH (y = win_rate)

Surrender-happy champions

Spot champions most associated with surrender losses.

SELECT COUNT(*) AS games, AVG(surrendered::INT) AS surrender_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY champion
ORDER BY surrender_rate DESC
LIMIT 10
RENDER leaderboard

Most-played champions

Show which champions the server has been playing most.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY champion
ORDER BY games DESC
LIMIT 10
RENDER bar_chart WITH (y = games)

Most active teammate groups

List teammate groups of every size (group(2) picks duos only) by games together.

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

KDA leaders

Rank players by KDA with a minimum games filter.

SELECT COUNT(*) AS games, kda() AS kda
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 5
ORDER BY kda DESC
LIMIT 10
RENDER leaderboard

Damage leaders

Find who dealt the most champion damage.

SELECT COUNT(*) AS games, SUM(total_damage_dealt_to_champions) AS damage_to_champions
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 14 DAY
GROUP BY player
ORDER BY damage_to_champions DESC
LIMIT 10
RENDER bar_chart WITH (y = damage_to_champions)

Champion-select picks

Use lobby observations to see planned champion picks.

SELECT COUNT(*) AS prematches
FROM prematch_participants
WHERE observed_at >= CURRENT_TIMESTAMP - INTERVAL 14 DAY
GROUP BY champion
ORDER BY prematches DESC
LIMIT 10
RENDER bar_chart WITH (y = prematches)

Queue mix

Break recent server activity down by queue.

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

Daily activity trend

Follow daily game volume with a smoothed filled trend.

SELECT DATE_TRUNC('day', game_creation_at) AS day, COUNT(*) AS games
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY DATE_TRUNC('day', game_creation_at)
ORDER BY day ASC
RENDER area_chart WITH (y = games, title = 'Daily games', palette = gold, smooth = true, trend = true, sparkline = true)

Weekly wins and losses

Compare weekly wins and losses as stacked bars.

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), palette = team, labels = value, compare = previous_period)

Win/loss share

Show the share of recent games by outcome.

SELECT COUNT(*) AS games
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY outcome
ORDER BY games DESC
RENDER donut_chart WITH (y = games, title = 'Recent outcomes', labels = percent)

Combat efficiency map

Compare player damage per game with KDA and game volume.

SELECT COUNT(*) AS games, AVG(total_damage_dealt_to_champions) AS damage_per_game, kda() AS kda
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 5
ORDER BY damage_per_game DESC
RENDER scatter_chart WITH (x = damage_per_game, y = kda, size = games, palette = colorblind)

Champion position heatmap

Reveal champion win-rate pockets across team positions.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 60 DAY
GROUP BY champion, team_position
HAVING games >= 3
ORDER BY games DESC
LIMIT 80
RENDER heatmap WITH (x = champion, series = team_position, value = win_rate, palette = ranked, labels = value)

Champion combat profiles

Compare per-game combat and vision profiles for active champions.

SELECT COUNT(*) AS games, AVG(kills) AS kills_per_game, AVG(assists) AS assists_per_game, AVG(total_damage_dealt_to_champions) AS damage_per_game, AVG(vision_score) AS vision_per_game
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY champion
HAVING games >= 5
ORDER BY games DESC
LIMIT 6
RENDER radar_chart WITH (y = (kills_per_game, assists_per_game, damage_per_game, vision_per_game), legend = top, palette = categorical)

Server KPI snapshot

Summarize activity, win rate, KDA, and game length in one card.

SELECT COUNT(*) AS games, AVG(win::INT) AS win_rate, kda() AS kda, AVG(game_duration_seconds) AS avg_game_duration
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
RENDER kpi_card WITH (y = (games, win_rate, kda, avg_game_duration), title = '30-day snapshot', theme = minimal_dark)

Damage per gold

Rank players by champion damage produced per gold earned.

SELECT COUNT(*) AS games, ROUND(SUM(total_damage_dealt_to_champions) / NULLIF(SUM(gold_earned), 0), 3) AS damage_per_gold
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 5
ORDER BY damage_per_gold DESC
LIMIT 15
RENDER bar_chart WITH (y = damage_per_gold, orientation = horizontal, palette = gold, labels = value)

Weekly vision trend

Track wards and vision score per game over time.

SELECT DATE_TRUNC('week', game_creation_at) AS week, AVG(vision_score) AS vision_per_game, AVG(wards_placed) AS wards_per_game, AVG(wards_killed) AS wards_killed_per_game
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 = (vision_per_game, wards_per_game, wards_killed_per_game), palette = colorblind, legend = bottom, smooth = true, rolling = 3)

Arena placement share

Break Arena games down by final placement.

SELECT COUNT(*) AS arena_games
FROM match_participants
WHERE queue = 'arena'
  AND game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY arena_placement
ORDER BY arena_placement ASC
RENDER donut_chart WITH (y = arena_games, palette = ranked, labels = percent)

Objective pressure leaders

Combine objective and turret damage into a per-game pressure score.

SELECT COUNT(*) AS games, AVG(damage_dealt_to_objectives + damage_dealt_to_turrets) AS objective_pressure
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 30 DAY
GROUP BY player
HAVING games >= 5
ORDER BY objective_pressure DESC
LIMIT 12
RENDER bar_chart WITH (y = objective_pressure, orientation = horizontal, palette = team)

First blood by position

Compare first-blood rates across team positions.

SELECT COUNT(*) AS games, AVG(first_blood_kill::INT) AS first_blood_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 60 DAY
GROUP BY team_position
HAVING games >= 10
ORDER BY first_blood_rate DESC
RENDER bar_chart WITH (y = first_blood_rate, palette = ranked, labels = percent)

Game length trend

Track average match duration by week.

SELECT DATE_TRUNC('week', game_creation_at) AS week, AVG(game_duration_seconds) AS avg_game_duration
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 120 DAY
GROUP BY DATE_TRUNC('week', game_creation_at)
ORDER BY week ASC
RENDER line_chart WITH (y = avg_game_duration, title = 'Average game length', y_axis = 'Minutes', smooth = true, trend = true)

Surrender trend

Follow total and early surrender rates by week.

SELECT DATE_TRUNC('week', game_creation_at) AS week, AVG(surrendered::INT) AS surrender_rate, AVG(early_surrendered::INT) AS early_surrender_rate
FROM match_participants
WHERE game_creation_at >= CURRENT_TIMESTAMP - INTERVAL 120 DAY
GROUP BY DATE_TRUNC('week', game_creation_at)
ORDER BY week ASC
RENDER area_chart WITH (y = (surrender_rate, early_surrender_rate), palette = colorblind, labels = hide, smooth = true, rolling = 3, compare = previous_period)

Game length distribution

Show how recent games are distributed across five-minute length buckets.

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, x_axis = 'Game length (seconds)')

Activity against the previous period

Overlay weekly game volume with the equally long span before it.

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

Related

• Query recipes — the idioms behind most reports.
• Build your first scheduled report
• Turn a report into a chart