Lesson 10 intermediate

CTEs and Window Functions

Write readable multi-step queries with common table expressions, generate series and walk hierarchies with recursive CTEs (and avoid the column-width trap), then rank, number and compare rows with window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD, running totals, shares of a total, frames and named windows, including the classic "latest row per group".

Lesson 9 collapsed rows into groups. Sometimes you need the opposite: keep every row but add information computed across other rows, such as “this is Ana’s 2nd note” or “7 days since the previous one”. That is what window functions do. Common table expressions, the other half of this lesson, make long queries readable. Both arrived in MySQL 8.0.

Common table expressions

A CTE is a named subquery defined with WITH at the top of a statement. It reads top to bottom like a recipe:

WITH per_user AS (
  SELECT user_id, COUNT(*) AS notes, MAX(created_at) AS last_note
  FROM notes
  GROUP BY user_id
)
SELECT u.name, p.notes, p.last_note
FROM users u
JOIN per_user p ON p.user_id = u.id
ORDER BY u.id;
+------------+-------+---------------------+
| name       | notes | last_note           |
+------------+-------+---------------------+
| Ana Silva  |     3 | 2026-11-02 10:00:00 |
| Ben Carter |     2 | 2026-10-12 08:45:00 |
+------------+-------+---------------------+
2 rows in set (0.00 sec)

Several CTEs are separated by commas, and later ones can use earlier ones:

WITH pro_users AS (
  SELECT id, name FROM users WHERE plan = 'pro'
),
pro_notes AS (
  SELECT n.* FROM notes n JOIN pro_users p ON p.id = n.user_id
)
SELECT COUNT(*) AS pro_notes FROM pro_notes;
+-----------+
| pro_notes |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

Compared with derived tables, a CTE can be referenced more than once in the same query and keeps each step named. The optimizer may inline it or compute it once, whichever is cheaper.

Recursive CTEs

A recursive CTE refers to itself. It has an anchor part that produces the first rows and a recursive part that produces more rows from the previous ones, joined by UNION ALL. It stops when the recursive part returns nothing.

Generating a series

Lesson 9 noted that months without notes vanish from a report. Generate the months first, then left join the notes:

WITH RECURSIVE months AS (
  SELECT DATE('2026-09-01') AS m
  UNION ALL
  SELECT m + INTERVAL 1 MONTH FROM months WHERE m < '2026-11-01'
)
SELECT DATE_FORMAT(m, '%Y-%m') AS month, COUNT(n.id) AS notes
FROM months
LEFT JOIN notes n ON n.created_at >= m AND n.created_at < m + INTERVAL 1 MONTH
GROUP BY m
ORDER BY m;
+---------+-------+
| month   | notes |
+---------+-------+
| 2026-09 |     0 |
| 2026-10 |     4 |
| 2026-11 |     1 |
+---------+-------+
3 rows in set (0.00 sec)

September now shows up with zero. The same pattern generates days, hours or number sequences.

Walking a hierarchy

Suppose Orbit adds nested folders, each with an optional parent:

CREATE TABLE folders (
  id        INT UNSIGNED PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name      VARCHAR(50) NOT NULL
);
INSERT INTO folders VALUES (1, NULL, 'Work'), (2, 1, 'Projects'), (3, 2, 'Orbit'), (4, NULL, 'Personal');

A recursive CTE starts at the top-level folders and walks down one level per step, building the full path:

WITH RECURSIVE tree AS (
  SELECT id, name, CAST(name AS CHAR(500)) AS path, 1 AS depth
  FROM folders WHERE parent_id IS NULL
  UNION ALL
  SELECT f.id, f.name, CONCAT(t.path, ' > ', f.name), t.depth + 1
  FROM folders f
  JOIN tree t ON f.parent_id = t.id
)
SELECT path, depth FROM tree ORDER BY path;
+-------------------------+-------+
| path                    | depth |
+-------------------------+-------+
| Personal                |     1 |
| Work                    |     1 |
| Work > Projects         |     2 |
| Work > Projects > Orbit |     3 |
+-------------------------+-------+
4 rows in set (0.00 sec)

Warning: The column types of a recursive CTE come from the anchor part only. Without the CAST(name AS CHAR(500)), path would be a VARCHAR(50) like name, and the longer paths would fail with “Data too long for column ‘path'”. Always widen columns that grow in the recursive part.

MySQL stops runaway recursion after cte_max_recursion_depth iterations (1000 by default); raise it per session for deep trees, and add a WHERE depth < n guard when the data could contain cycles.

DROP TABLE folders;

Window functions

A window function computes a value for each row from a set of related rows, its window, defined by OVER (...):

  • PARTITION BY splits rows into independent groups, like GROUP BY but without collapsing them.
  • ORDER BY orders rows inside each partition.
  • An optional frame (ROWS BETWEEN ...) limits which rows around the current one count.

Numbering rows

Number each user’s notes in the order they were written:

SELECT user_id, title,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS nth
FROM notes
ORDER BY user_id, nth;
+---------+------------------+-----+
| user_id | title            | nth |
+---------+------------------+-----+
|       1 | Shopping list    |   1 |
|       1 | Deploy checklist |   2 |
|       1 | Meeting notes    |   3 |
|       2 | Book ideas       |   1 |
|       2 | MySQL tips       |   2 |
+---------+------------------+-----+
5 rows in set (0.00 sec)

Latest row per group

Number in descending order and keep row 1, and you have the most-asked SQL question solved, “the latest note of each user”:

WITH ranked AS (
  SELECT user_id, title, created_at,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notes
)
SELECT user_id, title, created_at FROM ranked WHERE rn = 1 ORDER BY user_id;
+---------+---------------+---------------------+
| user_id | title         | created_at          |
+---------+---------------+---------------------+
|       1 | Meeting notes | 2026-11-02 10:00:00 |
|       2 | MySQL tips    | 2026-10-12 08:45:00 |
+---------+---------------+---------------------+
2 rows in set (0.00 sec)

You cannot filter on a window function in WHERE of the same query, because windows are computed after WHERE. That is why the CTE is needed.

ROW_NUMBER, RANK and DENSE_RANK

The three ranking functions differ only in how they treat ties:

SELECT id, pinned,
       ROW_NUMBER() OVER (ORDER BY pinned DESC, id) AS row_num,
       RANK()       OVER (ORDER BY pinned DESC)     AS rnk,
       DENSE_RANK() OVER (ORDER BY pinned DESC)     AS dense
FROM notes
ORDER BY pinned DESC, id;
+----+--------+---------+-----+-------+
| id | pinned | row_num | rnk | dense |
+----+--------+---------+-----+-------+
|  1 |      1 |       1 |   1 |     1 |
|  4 |      1 |       2 |   1 |     1 |
|  2 |      0 |       3 |   3 |     2 |
|  3 |      0 |       4 |   3 |     2 |
|  5 |      0 |       5 |   3 |     2 |
+----+--------+---------+-----+-------+
5 rows in set (0.00 sec)
  • ROW_NUMBER always counts 1, 2, 3; give it a unique sort order or the numbering of ties is arbitrary.
  • RANK gives ties the same number and then skips: 1, 1, 3.
  • DENSE_RANK gives ties the same number without gaps: 1, 1, 2.

(RANK is a reserved word in MySQL 8, which is why the alias is rnk.)

Comparing with the previous row: LAG and LEAD

LAG(col) reads the value from the previous row in the window, LEAD(col) from the next. How many days passed between each user’s notes?

SELECT user_id, title,
       DATEDIFF(created_at,
                LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)) AS days_since_prev
FROM notes
ORDER BY user_id, created_at;
+---------+------------------+-----------------+
| user_id | title            | days_since_prev |
+---------+------------------+-----------------+
|       1 | Shopping list    |            NULL |
|       1 | Deploy checklist |               2 |
|       1 | Meeting notes    |              30 |
|       2 | Book ideas       |            NULL |
|       2 | MySQL tips       |               7 |
+---------+------------------+-----------------+
5 rows in set (0.00 sec)

The first note of each user has no previous row, so the result is NULL. LAG(col, 2) looks two rows back, and LAG(col, 1, 0) returns 0 instead of NULL.

Running totals and shares

An aggregate with OVER becomes a window aggregate. With ORDER BY it accumulates:

SELECT DATE(created_at) AS day,
       COUNT(*) OVER (ORDER BY created_at) AS notes_so_far
FROM notes
ORDER BY created_at;
+------------+--------------+
| day        | notes_so_far |
+------------+--------------+
| 2026-10-01 |            1 |
| 2026-10-03 |            2 |
| 2026-10-05 |            3 |
| 2026-10-12 |            4 |
| 2026-11-02 |            5 |
+------------+--------------+
5 rows in set (0.00 sec)

With an empty OVER () the window is the whole result, which gives each group’s share of the total in one pass:

SELECT user_id,
       COUNT(*) AS notes,
       ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM notes
GROUP BY user_id
ORDER BY user_id;
+---------+-------+------+
| user_id | notes | pct  |
+---------+-------+------+
|       1 |     3 | 60.0 |
|       2 |     2 | 40.0 |
+---------+-------+------+
2 rows in set (0.00 sec)

Window functions run after GROUP BY, so they can work on aggregates like COUNT(*).

Frames and named windows

By default, an ordered window runs from the start of the partition to the current row. A frame changes that, for example a moving average over the current and two previous rows:

AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

When several functions share a window, name it once with WINDOW:

SELECT id,
       FIRST_VALUE(title) OVER w AS first_title,
       LAST_VALUE(title)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_title
FROM notes
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

LAST_VALUE needs the explicit frame: with the default frame, the “last” row is always the current one.

Window functions at a glance

FunctionGives
ROW_NUMBER()1, 2, 3 within the partition
RANK() / DENSE_RANK()Rank with / without gaps after ties
NTILE(n)Bucket number 1 to n (quartiles with NTILE(4))
PERCENT_RANK() / CUME_DIST()Relative position from 0 to 1
LAG(col, n, default) / LEAD(...)Value n rows before / after
FIRST_VALUE / LAST_VALUE / NTH_VALUEValue at a position in the frame
SUM, AVG, COUNT, MIN, MAX with OVERRunning or partition-wide aggregates

Exercise

Why does WHERE ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1 fail?

  1. Window functions are computed after WHERE, so they cannot be used there
  2. ROW_NUMBER needs a frame clause
  3. PARTITION BY is not allowed with DESC
  4. MySQL only supports window functions in ORDER BY
Show answer

Answer: A. Windows are evaluated after FROM, WHERE, GROUP BY and HAVING. Compute the row number in a CTE or derived table and filter in the outer query.

Exercise

Scores 90, 90 and 80 are ranked in descending order. What does DENSE_RANK return for 80?

  1. 1
  2. 2
  3. 3
  4. NULL
Show answer

Answer: B. DENSE_RANK gives the two 90s rank 1 and continues without a gap, so 80 is 2. RANK would return 3.

Exercise

A recursive CTE builds a path with CONCAT and fails with “Data too long for column ‘path'”. What is the fix?

  1. Increase cte_max_recursion_depth
  2. Use UNION instead of UNION ALL
  3. CAST the path in the anchor part to a wider type such as CHAR(500)
  4. Add ORDER BY to the recursive part
Show answer

Answer: C. Column types come from the anchor query. Widening the column there leaves room for the longer values the recursive part produces.

Key takeaways

  • CTEs (WITH) name each step of a complex query; recursive CTEs generate series and walk trees.
  • Widen columns in the recursive CTE’s anchor part, and know the 1000-step recursion limit.
  • Window functions keep every row and add values computed over a partition.
  • “Latest row per group” is ROW_NUMBER() ... DESC in a CTE, filtered on rn = 1 outside.
  • LAG/LEAD compare neighbours; aggregates with OVER give running totals and shares.

FAQ

Are CTEs slower than subqueries?

No. MySQL treats a non-recursive CTE like a derived table and may merge it into the outer query. If a CTE is referenced several times, MySQL can materialise it once and reuse it, which can even be faster.

Can I use window functions in UPDATE or DELETE?

Not directly, but you can join to a CTE or derived table that computes them. For example, delete all but the latest note per user by joining notes to a ranked CTE and deleting rows where rn > 1.

Which MySQL version do I need?

CTEs and window functions arrived in MySQL 8.0. MariaDB supports them from 10.2. MySQL 5.7 has neither; queries there need user variables or self joins instead.

What is the difference between ROWS and RANGE frames?

ROWS counts physical rows around the current one. RANGE uses values of the ORDER BY column, so all rows with the same value are treated as peers, and you can write frames like RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW.

↑ ↓ navigate Enter open Esc close