SQL window functions (OVER, PARTITION BY)

A GROUP BY reduces the rows: one row per group. But how do you show each product with its rank in its category, or each month with the revenue accumulated since January? Window functions compute over a set of rows without merging them: each row keeps its identity.

Syntax

FUNCTION() OVER (
  PARTITION BY group_column
  ORDER BY sort_column
)

How it works

The OVER (…) clause defines the “window”, that is, the rows the function looks at:

• PARTITION BY splits the rows into groups, like a GROUP BY, but without merging them.
• ORDER BY sets the order of the rows within each group.

Ranking functions

Window aggregates

SUM(…) OVER (ORDER BY …) gives a running total. AVG(…) OVER (PARTITION BY …) shows the group average next to each row. LAG() and LEAD() read the value of the previous or next row.

Window functions are computed after the WHERE and the GROUP BY. To filter on their result (for example “the top 3”), you have to go through a subquery or a CTE.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

SELECT categorie, nom, prix,
       RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) AS rang
FROM produits
LIMIT 10;

WITH ventes AS (
  SELECT p.categorie, p.nom, SUM(l.quantite) AS vendus,
         ROW_NUMBER() OVER (PARTITION BY p.categorie ORDER BY SUM(l.quantite) DESC) AS rn
  FROM produits p JOIN lignes_commande l ON l.produit_id = p.id
  GROUP BY p.id
)
SELECT categorie, nom, vendus
FROM ventes
WHERE rn <= 2;

WITH mois AS (
  SELECT strftime('%Y-%m', o.date_commande) AS mois, SUM(l.quantite * l.prix_unitaire) AS ca
  FROM commandes o JOIN lignes_commande l ON l.commande_id = o.id
  WHERE o.date_commande LIKE '2025%'
  GROUP BY mois
)
SELECT mois, ROUND(ca, 2) AS ca, ROUND(SUM(ca) OVER (ORDER BY mois), 2) AS cumul
FROM mois
LIMIT 6;

SELECT departement, nom, salaire,
       ROUND(AVG(salaire) OVER (PARTITION BY departement)) AS moyenne_dept
FROM employes
LIMIT 8;

Common mistakes

SELECT nom, RANK() OVER (ORDER BY prix DESC) AS r FROM produits WHERE RANK() OVER (ORDER BY prix DESC) <= 3;

SELECT * FROM (SELECT nom, RANK() OVER (ORDER BY prix DESC) AS r FROM produits) WHERE r <= 3;

SELECT categorie, nom, RANK() OVER (ORDER BY prix DESC) FROM produits;

SELECT categorie, nom, RANK() OVER (PARTITION BY categorie ORDER BY prix DESC) FROM produits;

SELECT * FROM (SELECT nom, RANK() OVER (ORDER BY stock) AS r FROM produits) WHERE r <= 1;

SELECT * FROM (SELECT nom, ROW_NUMBER() OVER (ORDER BY stock, id) AS r FROM produits) WHERE r <= 1;

Exercises

Try writing the query yourself before opening the solution.

SELECT client_id, id, date_commande, ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY date_commande) AS num FROM commandes LIMIT 10;

SELECT departement, prenom, nom, salaire FROM (SELECT *, RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS r FROM employes) WHERE r = 1;

Frequently asked questions