Window functions on a CSV, in the browser

The intro piece — Query a CSV with SQL, right in your browser — covered getting a CSV into the SQL Playground and answering questions with GROUP BY, a JOIN and a first window function. This is the sequel: the window-function toolkit that turns "compared to what" questions into one query each. The Playground is SQLite 3.49 compiled to WebAssembly, so everything below is stock SQLite — window functions, frame clauses, recursive CTEs — running in the tab.

One running example throughout. A latency export, imported as a table named latency:

day,service,p50_ms
2026-08-01,api,42
2026-08-01,auth,18
2026-08-02,api,44
2026-08-02,auth,17
2026-08-03,api,51
2026-08-05,api,49
2026-08-05,auth,21
2026-08-06,api,47

(Note the missing days — we'll come back for them.)

One thing before anything else: imported CSV columns are TEXT. The importer creates every column as TEXT, so p50_ms sorts and compares as a string — "9" > "44". Cast at the point of use (CAST(p50_ms AS REAL)) or, cleaner, make a typed copy once and query that:

CREATE TABLE lat AS
SELECT day, service, CAST(p50_ms AS REAL) AS p50
FROM latency;

ROW_NUMBER and RANK: dedup and top-N per group

Duplicate rows from a re-run export? Number each group of duplicates and keep row one:

SELECT day, service, p50
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY day, service ORDER BY day
  ) AS rn
  FROM lat
)
WHERE rn = 1;

The same shape answers "worst two days per service" — the question that's a nightmare in a spreadsheet and a subquery here:

SELECT service, day, p50
FROM (
  SELECT *, RANK() OVER (
    PARTITION BY service ORDER BY p50 DESC
  ) AS r
  FROM lat
)
WHERE r <= 2;

ROW_NUMBER always gives 1, 2, 3…; RANK gives ties the same number and skips ahead after them. For dedup you want ROW_NUMBER (exactly one survivor); for leaderboards you usually want RANK (ties deserve the same rank).

LAG: the delta between a row and the one before

"How much did latency move day over day?" is a self-join in old SQL and one function now:

SELECT day, service, p50,
       p50 - LAG(p50) OVER (
         PARTITION BY service ORDER BY day
       ) AS delta
FROM lat;

The first row of each partition has no predecessor, so its delta is NULL — correct, and a useful reminder that LAG(p50, 1, 0) (third argument = default) would silently claim day one improved from zero.

Moving averages: frame clauses

ORDER BY inside OVER() defines the order; the frame defines which neighbours each row sees. A 7-row trailing average:

SELECT day, service, p50,
       AVG(p50) OVER (
         PARTITION BY service ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS p50_7d
FROM lat;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means "this row and up to six before it". Early rows average whatever exists so far — fine for a chart, but know it's happening. And note that with gaps in the data, 7 rows is not 7 days; that's what the gap-filling below fixes.

NTILE: percentile buckets

Split each service's days into quartiles — which days were its worst 25%?

SELECT day, service, p50,
       NTILE(4) OVER (
         PARTITION BY service ORDER BY p50
       ) AS quartile
FROM lat;

quartile = 4 is the worst bucket. NTILE(100) gives you percentile ranks the same way.

Recursive CTE: fill the date gaps

August 3rd and 4th are missing for auth; the 4th is missing entirely. A recursive CTE generates the full calendar, then a LEFT JOIN shows the holes:

WITH RECURSIVE days(d) AS (
  SELECT '2026-08-01'
  UNION ALL
  SELECT date(d, '+1 day') FROM days
  WHERE d < '2026-08-06'
),
services AS (SELECT DISTINCT service FROM lat)
SELECT days.d AS day, services.service, lat.p50
FROM days
CROSS JOIN services
LEFT JOIN lat ON lat.day = days.d AND lat.service = services.service
ORDER BY services.service, days.d;

Every service now has a row for every day, with NULL where nothing was recorded — the honest input for that moving average, and the shape a charting tool wants. Press Send and the result set travels onward as CSV: to Convert for JSON, or back out as a clean gap-filled file.

The traps

Aggregate vs window versions of the same function. SUM, AVG, COUNT, MIN, MAX all exist in both forms, and only the OVER clause distinguishes them. SELECT service, AVG(p50) FROM lat GROUP BY service collapses to one row per service; AVG(p50) OVER (PARTITION BY service) keeps every row and stamps the group average onto each. Mix them carelessly — a windowed column next to a GROUP BY — and SQLite computes the window over the grouped result, which is almost never what you meant. If a query has both, write the aggregate in a CTE and the window function over the CTE, in that order; that's also the order the engine evaluates them.

ORDER BY inside OVER() turns SUM into a running total. SUM(p50) OVER (PARTITION BY service) is the partition total on every row. Add an ORDER BY — SUM(p50) OVER (PARTITION BY service ORDER BY day) — and it becomes a running total, because an ORDER BY without an explicit frame implies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Same function, one clause, completely different numbers. If you want the full-partition total and an order (say, for RANK in the same query), spell the frame out: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. When a running total appears where you expected a constant, the ORDER BY you added "for tidiness" is the culprit.