ScoutQL functions

These are the names ScoutQL can call. There is no implicit aggregation: a column named on its own in SELECT is a raw value, and a number per group is always an aggregate you wrote.

That is the trade the language makes. It costs six extra characters to say COUNT(*), and in exchange every number in a report says what it is, derived statistics need no new vocabulary, and a query means the same thing here as it would in any SQL console.

Aggregates

An aggregate collapses the rows of a group into one number.

Signature	Returns	Description
`COUNT(*)`	BIGINT	Row count. COUNT(*) counts rows, COUNT(x) non-NULL values, COUNT(DISTINCT x) distinct values.
`COUNT(x)`
`COUNT(DISTINCT x)`
`SUM(x)`	BIGINT for integer input, DOUBLE otherwise	Sum of a numeric expression. Booleans need an explicit cast: SUM(win::INT).
`AVG(x)`	DOUBLE	Arithmetic mean. Rates are AVG(flag::INT) — e.g. AVG(win::INT) is the win rate.
`MIN(x)`	same as the argument	Smallest value.
`MAX(x)`	same as the argument	Largest value.
`MEDIAN(x)`	DOUBLE	Median (50th percentile).
`QUANTILE_CONT(x, q)`	DOUBLE	Continuous (interpolated) quantile — QUANTILE_CONT(damage, 0.9) is the 90th percentile.
`STDDEV(x)`	DOUBLE	Sample standard deviation.

Three things apply to all of them.

FILTER (WHERE …) narrows one aggregate without narrowing the query. Wins, losses, and a solo-queue win rate can share a single pass over the same rows, which is both faster and directly comparable — three separate queries could each be measuring a different set of games.

SELECT COUNT(*) AS games,
       COUNT(*) FILTER (WHERE win) AS wins,
       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

DISTINCT counts unique values. COUNT(DISTINCT champion_id) is a champion pool size. It is not additive, so it cannot be accumulated across buckets with cumulative.

A rate is the average of a cast boolean. DuckDB will not average a BOOLEAN, so AVG(win::INT) is required — and the cast is what turns a count of wins into a fraction of games.

Scalar functions

A scalar function transforms a single value, inside an aggregate’s argument, a grouping expression, or a predicate.

Signature	Returns	Description
`ROUND(x[, digits])`	DOUBLE	Round to a number of decimal places.
`FLOOR(x)`	same numeric class as the argument	Round down. FLOOR(x / w) * w is the bucketing idiom for histograms.
`CEIL(x)`	same numeric class as the argument	Round up.
`ABS(x)`	same as the argument	Absolute value.
`COALESCE(a, b, …)`	common type of the arguments	First non-NULL argument.
`NULLIF(a, b)`	type of the first argument	NULL when a = b, else a. x / NULLIF(y, 0) avoids division by zero.
`GREATEST(a, b, …)`	common type of the arguments	Row-wise maximum of its arguments.
`LEAST(a, b, …)`	common type of the arguments	Row-wise minimum of its arguments.
`DATE_TRUNC('day'\|'week'\|'month', ts)`	TIMESTAMP	Truncate a timestamp to a calendar bucket. Time-series queries GROUP BY DATE_TRUNC('week', game_creation_at).

FLOOR(x / width) * width is the bucketing idiom: dividing assigns the bucket, multiplying restores its real starting value, so the axis reads in the column’s own units rather than in bucket indexes.

Macros

Macros stand for an expression that is easy to get subtly wrong. They expand at compile time into ordinary SQL, so they behave exactly like the expression they name — including under FILTER (WHERE …).

Signature	Returns	Description
`kda()`	DOUBLE	Aggregate KDA: (SUM(kills) + SUM(assists)) / GREATEST(SUM(deaths), 1) — a perfect KDA shows takedowns.
`per_minute(x)`	DOUBLE	Rate per minute played: SUM(x) / NULLIF(SUM(time_played) / 60, 0).

kda() divides by at least one death, which is why a deathless streak still ranks by takedowns instead of dividing by zero. per_minute(x) guards the same way against a group with no recorded playtime.

Match references

These two resolve a human name to the thing the data actually stores.

Signature	Returns	Description
`player('name')`	BOOLEAN (WHERE only)	Filter to one tracked player's rows: WHERE player('Bob') (or WHERE player = player('Bob')). The name resolves at run time.
`champion('Name')`	INTEGER constant (a champion_id)	Resolve a champion display name to its numeric id at compile time: WHERE champion_id = champion('Jinx').

champion('…') folds to a numeric id when the query compiles, so a misspelling is caught with a suggestion before the report ever runs. player('…') resolves when the report runs, so it follows renames and newly linked accounts on its own.

Casts