Sign in Get started

SQL Tutorial · Advanced SQL · Lesson 35 of 36

SQL Window Functions

ROW_NUMBER, RANK, running totals and LAG with OVER (PARTITION BY ...).

Aggregates without collapsing rows

A window function computes a value across a set of related rows while keeping every row. The OVER (...) clause defines the window:

  • PARTITION BY — split rows into groups (like GROUP BY, without collapsing).
  • ORDER BY — order inside each partition.
Example 1Try it yourself
SELECT name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department)) AS dept_avg,
       ROUND(salary - AVG(salary) OVER (PARTITION BY department)) AS diff
FROM employees
WHERE department IN ('Engineering', 'Sales')
ORDER BY department, salary DESC;

Output

name | department | salary | dept_avg | diff
Omar Haddad | Engineering | 210000 | 148400 | 61600
Mei Chen | Engineering | 165000 | 148400 | 16600
Jonas Berg | Engineering | 140000 | 148400 | -8400
Priya Nair | Engineering | 115000 | 148400 | -33400
Lucas Martin | Engineering | 112000 | 148400 | -36400
Hannah Okafor | Sales | 150000 | 109333 | 40667
Diego Silva | Sales | 90000 | 109333 | -19333
Emma Novak | Sales | 88000 | 109333 | -21333

Ranking and top-N per group

ROW_NUMBER(), RANK() and DENSE_RANK() number rows within each partition — the standard way to get "top N per group":

Example 2Try it yourself
SELECT department, name, salary, rnk
FROM (
  SELECT department, name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) AS ranked
WHERE rnk <= 2
ORDER BY department, rnk;

Output

department | name | salary | rnk
Engineering | Omar Haddad | 210000 | 1
Engineering | Mei Chen | 165000 | 2
Management | Sofia Reyes | 250000 | 1
Sales | Hannah Okafor | 150000 | 1
Sales | Diego Silva | 90000 | 2
Support | Ravi Kumar | 65000 | 1

Running totals and LAG

Example 3Try it yourself
WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY month
)
SELECT month, ROUND(revenue, 2) AS revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total,
       ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS change
FROM monthly ORDER BY month;

Output

month | revenue | running_total | change
2026-01 | 167.98 | 167.98 | NULL
2026-02 | 561.38 | 729.36 | 393.4
2026-03 | 114.99 | 844.35 | -446.39
2026-04 | 747 | 1591.35 | 632.01
2026-05 | 131.45 | 1722.8 | -615.55
2026-06 | 102 | 1824.8 | -29.45

Window functions are among the most-asked SQL interview topics.

Test yourself

Which clause splits rows into groups for a window function?

  1. GROUP BY
  2. PARTITION BY
  3. SPLIT BY
  4. WINDOW BY
Show answer

Answer: PARTITION BY