sqlstreak

SQL window functions

Window functions calculate across related rows while keeping every row. They're behind rankings, running totals and “compared with last time” reports, and they're a favourite in data-analyst interviews.

OVER and PARTITION BY

Add OVER (…) to a function to make it a window function. PARTITION BY sets which rows are related; ORDER BY inside OVER sets their order. Even ordinary aggregates work this way.

SELECT name, species, age,
  MAX(age) OVER (PARTITION BY species) AS oldest_in_species
FROM pets;

ROW_NUMBER vs RANK

ROW_NUMBER() always counts 1, 2, 3. RANK() gives ties the same number and then skips (1, 1, 3); DENSE_RANK() doesn't skip (1, 1, 2).

SELECT name, age,
  ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,
  RANK() OVER (ORDER BY age DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense
FROM pets;

Top N per group

Number rows within each group, then keep the first few in an outer query. Window functions can't go directly in WHERE, which is why the subquery is needed.

SELECT species, name, age
FROM (
  SELECT species, name, age,
    ROW_NUMBER() OVER (PARTITION BY species ORDER BY age DESC) AS rn
  FROM pets
) ranked
WHERE rn = 1;

Running totals and LAG

SUM(x) OVER (ORDER BY …) adds everything so far. LAG(x) reads the previous row's value (NULL on the first row); LEAD(x) reads the next.

SELECT id, ordered_on,
  COUNT(*) OVER (ORDER BY id) AS orders_so_far,
  LAG(ordered_on) OVER (PARTITION BY customer_id ORDER BY ordered_on, id) AS previous_order
FROM orders
ORDER BY id;

Common mistakes

  • Trying to filter on a window function in WHERE. Wrap the query and filter outside it.

  • Using ROW_NUMBER when ties should share a place. Use RANK or DENSE_RANK.

  • Forgetting ORDER BY inside OVER for running totals, which gives the grand total on every row.

Questions

Does SQLite support window functions?+

Yes, since version 3.25 (2018), with the same syntax as PostgreSQL for everything on this page.

Window function or GROUP BY?+

GROUP BY returns one row per group. A window function keeps every row and adds the calculation alongside it.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now