MySQL window functions and CTEs: 12 interview questions solved

Window functions and common table expressions (CTEs) are the two SQL features interviewers reach for when they want to see whether you can go beyond GROUP BY. In MySQL they have been available since 8.0, and everything here runs on MySQL 8.4 LTS. Below are the questions that come up most, each with a short answer you can say out loud and a query you can write on a whiteboard.

Every query and result in this article was run on MySQL 8.4.11 against the small orders table below, so you can paste it into a local server and follow along.

CREATE TABLE orders (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  ordered_on  DATE NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  KEY (customer_id, ordered_on)
);

INSERT INTO orders VALUES
 (1,10,'2026-07-03',120.00), (2,10,'2026-08-11', 80.00), (3,10,'2026-09-02',200.00),
 (4,20,'2026-07-15',200.00), (5,20,'2026-09-20',200.00),
 (6,30,'2026-08-01', 50.00), (7,30,'2026-08-19', 75.00), (8,30,'2026-09-28', 60.00);

Window function questions

1. What is a window function, and how is it different from GROUP BY?

A window function computes a value across a set of rows related to the current row, but it does not collapse those rows. GROUP BY returns one row per group; a window function returns every input row with an extra column. The set of rows is defined by OVER (PARTITION BY ... ORDER BY ... frame). OVER () with nothing inside treats the whole result as one window.

2. Explain ROW_NUMBER(), RANK() and DENSE_RANK()

All three number rows in window order. They differ only on ties (peers): ROW_NUMBER() gives peers different numbers, RANK() gives them the same number and leaves gaps, and DENSE_RANK() gives them the same number without gaps. Customers 10 and 20 both spent 400.00:

SELECT customer_id, SUM(amount) AS total,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk
FROM orders
GROUP BY customer_id
WINDOW w AS (ORDER BY SUM(amount) DESC)
ORDER BY total DESC, customer_id;
-- customer_id | total  | row_num | rnk | dense_rnk
--          10 | 400.00 |       1 |   1 |         1
--          20 | 400.00 |       2 |   1 |         1
--          30 | 185.00 |       3 |   3 |         2

Two bonus points: window functions are evaluated after GROUP BY, so you can rank aggregates as above; and the named WINDOW w AS (...) clause avoids repeating the same specification. Note also that the order of tied rows under ROW_NUMBER() is not deterministic unless the ORDER BY has a tie-breaker.

3. Return the latest order for each customer

This is the classic "greatest-n-per-group" question. Number rows per customer, newest first, then keep row 1:

WITH ranked AS (
  SELECT id, customer_id, ordered_on, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY ordered_on DESC, id DESC) AS rn
  FROM orders
)
SELECT id, customer_id, ordered_on, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;
-- 3 | 10 | 2026-09-02 | 200.00
-- 5 | 20 | 2026-09-20 | 200.00
-- 8 | 30 | 2026-09-28 |  60.00

Use RANK() instead if the interviewer wants all rows when two orders share the latest date.

4. Why can't you filter on a window function in WHERE?

Because of evaluation order. MySQL determines rows from FROM, then applies WHERE, GROUP BY and HAVING, and only then computes windows, before ORDER BY, LIMIT and DISTINCT. Window functions are allowed only in the select list and ORDER BY. Trying anyway fails:

SELECT id FROM orders WHERE ROW_NUMBER() OVER (ORDER BY id) = 1;
-- ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.

The fix is the pattern from question 3: compute the window in a CTE or derived table and filter in the outer query.

5. Write a running total. What frame does it use?

SELECT customer_id, ordered_on, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_on
                         ROWS UNBOUNDED PRECEDING) AS running_total,
       amount - LAG(amount) OVER (PARTITION BY customer_id
                                  ORDER BY ordered_on) AS change_from_prev
FROM orders
ORDER BY customer_id, ordered_on;
-- 10 | 2026-07-03 | 120.00 | 120.00 |   NULL
-- 10 | 2026-08-11 |  80.00 | 200.00 | -40.00
-- 10 | 2026-09-02 | 200.00 | 400.00 | 120.00
-- 20 | 2026-07-15 | 200.00 | 200.00 |   NULL
-- ...

The strong answer mentions the default frame. With ORDER BY and no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peers of the current row. If two rows share an ordered_on value, both get the same running total. ROWS UNBOUNDED PRECEDING counts physical rows instead, which is usually what a running total means. Without ORDER BY, the default frame is the whole partition.

6. What do LAG() and LEAD() do?

LAG(expr, N, default) returns expr from N rows before the current row in the partition; LEAD() looks N rows ahead. N defaults to 1 and the default value to NULL, which is why the first order of each customer shows NULL above. Typical uses are period-over-period change and gaps between events. Both ignore any frame clause and always work on the whole partition.

7. Moving average and percentage of total

WITH monthly AS (
  SELECT DATE_FORMAT(ordered_on, '%Y-%m') AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY month
)
SELECT month, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3m,
       ROUND(100 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM monthly
ORDER BY month;
-- 2026-07 | 320.00 | 320.00 | 32.5
-- 2026-08 | 205.00 | 262.50 | 20.8
-- 2026-09 | 460.00 | 328.33 | 46.7

The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a three-row window; the first rows simply average fewer values. SUM(revenue) OVER () is the grand total repeated on every row.

8. Why does LAST_VALUE() return the current row's value?

A favourite trick question. FIRST_VALUE(), LAST_VALUE() and NTH_VALUE() respect the frame, and the default frame with ORDER BY ends at the current row. So the "last" value is the current one:

SELECT ordered_on, amount,
  LAST_VALUE(amount) OVER (ORDER BY ordered_on) AS last_default,
  LAST_VALUE(amount) OVER (ORDER BY ordered_on
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM orders WHERE customer_id = 10 ORDER BY ordered_on;
-- 2026-07-03 | 120.00 | 120.00 | 200.00
-- 2026-08-11 |  80.00 |  80.00 | 200.00
-- 2026-09-02 | 200.00 | 200.00 | 200.00

By contrast, ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), NTILE(), PERCENT_RANK() and CUME_DIST() ignore the frame entirely.

CTE questions

9. What is a CTE and why use one?

A common table expression is a named, temporary result set defined with WITH and visible only to the statement that follows. Compared with a nested derived table, a CTE reads top to bottom, can be referenced more than once in the same statement, and can refer to CTEs defined earlier in the same WITH clause. Compared with a view, it lives only for one statement and needs no privileges to create.

10. Walk a hierarchy with a recursive CTE

A recursive CTE has a non-recursive anchor SELECT, then UNION ALL (or UNION DISTINCT), then a recursive SELECT that references the CTE once, in its FROM clause.

CREATE TABLE categories (
  id INT PRIMARY KEY, parent_id INT NULL, name VARCHAR(50) NOT NULL, KEY (parent_id)
);
INSERT INTO categories VALUES
 (1,NULL,'Software'), (2,1,'Databases'), (3,2,'MySQL'),
 (4,2,'PostgreSQL'), (5,1,'DevOps'), (6,5,'CI/CD');

WITH RECURSIVE tree (id, name, depth, path) AS (
  SELECT id, name, 0, CAST(name AS CHAR(500))
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1, CONCAT(t.path, ' > ', c.name)
  FROM tree AS t JOIN categories AS c ON c.parent_id = t.id
)
SELECT id, depth, path FROM tree ORDER BY path;
-- 1 | 0 | Software
-- 2 | 1 | Software > Databases
-- 3 | 2 | Software > Databases > MySQL
-- 4 | 2 | Software > Databases > PostgreSQL
-- 5 | 1 | Software > DevOps
-- 6 | 2 | Software > DevOps > CI/CD

Two details interviewers look for. First, column types come from the anchor part only, so the CAST(name AS CHAR(500)) makes room for the longer paths built later; without it, longer values are truncated, or the statement fails with error 1406 in strict mode. Second, the recursive part may not use aggregate functions, window functions, GROUP BY, ORDER BY or DISTINCT.

11. Fill gaps in a time series

Reports often need a row for every month, including empty ones. Generate the calendar recursively and left join the data:

WITH RECURSIVE months (m) AS (
  SELECT DATE '2026-06-01'
  UNION ALL
  SELECT m + INTERVAL 1 MONTH FROM months WHERE m < DATE '2026-09-01'
)
SELECT DATE_FORMAT(months.m, '%Y-%m') AS month,
       COALESCE(SUM(o.amount), 0)     AS revenue
FROM months
LEFT JOIN orders AS o
  ON o.ordered_on >= months.m
 AND o.ordered_on <  months.m + INTERVAL 1 MONTH
GROUP BY months.m
ORDER BY months.m;
-- 2026-06 |   0.00
-- 2026-07 | 320.00
-- 2026-08 | 205.00
-- 2026-09 | 460.00

Notice the half-open date range in the join. Comparing the bare column keeps the condition index-friendly: an index that starts with ordered_on could serve it, whereas wrapping the column in a function such as DATE_FORMAT() rules that out.

12. What stops a recursive CTE from running forever?

The cte_max_recursion_depth system variable, default 1000. A CTE with no termination condition fails:

WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte)
SELECT COUNT(*) FROM cte;
-- ERROR 3636 (HY000): Recursive query aborted after 1001 iterations.
--   Try increasing @@cte_max_recursion_depth to a larger value.

You can raise it per session, bound a SELECT by time with max_execution_time or the MAX_EXECUTION_TIME optimizer hint, or put a LIMIT in the recursive part. For graphs that may contain cycles, UNION DISTINCT stops revisiting identical rows.

Performance follow-ups

Expect at least one "how does this perform?" question. Good points to make:

• Each window needs sorted input. EXPLAIN FORMAT=TREE shows a Sort step under each Window aggregate, and a query with two different windows adds a temporary table between them. Reuse one named window where possible.
• Filter first. WHERE runs before windowing, so every row you exclude there is a row the server does not sort.
• Keep the recursive step indexed. The join in question 10 runs once per level, so an index on parent_id matters.

Quick revision table

How to prepare

Run the setup script above, then rewrite each answer without looking: change the partition, swap ROWS for RANGE, add a duplicate date and watch the running total change. Being able to explain why a result changed is what separates a memorised answer from a convincing one.