CTEs and Window Functions

MySQL 8.0 added Common Table Expressions (WITH, including WITH RECURSIVE) and window functions (OVER).

Concept

MySQL 8.0 added Common Table Expressions (WITH, including WITH RECURSIVE) and window functions (OVER). These are the most important modern SQL features for readable, analytical queries.

Why It Matters

Rankings, running totals, top-N per group, period comparisons, hierarchies, and deduplication become simple and fast. Before MySQL 8, these required user-variable tricks that were error-prone and are now deprecated.

Syntax

WITH [RECURSIVE]
    cte1 [(cols)] AS (SELECT ...),
    cte2 AS (SELECT ... FROM cte1)
SELECT ... FROM cte2;

fn(...) OVER ([PARTITION BY ...] [ORDER BY ...] [ROWS|RANGE BETWEEN ... AND ...])
fn(...) OVER w ... WINDOW w AS (...)

Example

14.2 Recursive CTEs The anchor's column types define the CTE columns: CAST(... AS CHAR(500)) makes room for longer paths (otherwise you get "Data too long" in strict mode).

cte_max_recursion_depth (default 1000) stops runaway recursion; 8.0.19+ also allows LIMIT in the recursive CTE.

Date series (fill missing days):

14.3 Ranking functions Also available: PERCENT_RANK(), CUME_DIST().

14.4 Top-N per group Window functions cannot appear in WHERE; filter in an outer query or CTE. (MySQL has no QUALIFY clause.) 14.5 LAG and LEAD

14.6 Running totals, moving averages, share of total 14.7 FIRST_VALUE, LAST_VALUE, NTH_VALUE

Without the explicit frame, LAST_VALUE returns the current row (default frame ends at CURRENT ROW).

14.8 Replacing old user-variable tricks Setting user variables inside expressions is deprecated since 8.0.

WITH dept_stats AS (
    SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
)
SELECT d.dept_name, s.headcount, ROUND(s.avg_salary, 2) AS avg_salary
FROM dept_stats s
JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.avg_salary DESC, d.dept_name;

+-------------+-----------+------------+
| dept_name   | headcount | avg_salary |
+-------------+-----------+------------+
| Engineering |         4 |  147500.00 |
| HR          |         1 |   90000.00 |
| Sales       |         2 |   90000.00 |
+-------------+-----------+------------+

-- Number series
WITH RECURSIVE nums (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 5
)
SELECT n FROM nums;             -- 1..5

-- Management hierarchy
WITH RECURSIVE org AS (
    SELECT emp_id, first_name, manager_id, 1 AS lvl, CAST(first_name AS CHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.first_name, e.manager_id, o.lvl + 1, CONCAT(o.path, ' > ', e.first_name)
    FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SELECT lvl, path FROM org ORDER BY path;

+-----+-----------------------+
| lvl | path                  |
+-----+-----------------------+
|   1 | Asha                  |
|   2 | Asha > Arjun          |
|   2 | Asha > Kiran          |
|   2 | Asha > Rahul          |
|   3 | Asha > Rahul > Priya  |
|   2 | Asha > Vikram         |
|   3 | Asha > Vikram > Meera |
|   3 | Asha > Vikram > Neha  |
+-----+-----------------------+

WITH RECURSIVE days AS (
    SELECT DATE('2026-03-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-03-31'
)
SELECT d.d, COUNT(o.order_id) AS orders
FROM days d LEFT JOIN orders o ON o.order_date = d.d
GROUP BY d.d
ORDER BY d.d;      -- 31 rows; 1 order on 2026-03-01 and 2026-03-10

SELECT first_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)         AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)         AS dense_rnk,
       NTILE(4)     OVER (ORDER BY salary DESC, emp_id) AS quartile
FROM employees
ORDER BY row_num;

+------------+-----------+---------+-----+-----------+----------+
| first_name | salary    | row_num | rnk | dense_rnk | quartile |
+------------+-----------+---------+-----+-----------+----------+
| Asha       | 250000.00 |       1 |   1 |         1 |        1 |
| Vikram     | 150000.00 |       2 |   2 |         2 |        1 |
| Rahul      | 120000.00 |       3 |   3 |         3 |        2 |
| Neha       |  95000.00 |       4 |   4 |         4 |        2 |
| Meera      |  95000.00 |       5 |   4 |         4 |        3 |
| Arjun      |  90000.00 |       6 |   6 |         5 |        3 |
| Kiran      |  80000.00 |       7 |   7 |         6 |        4 |
| Priya      |  60000.00 |       8 |   8 |         7 |        4 |
+------------+-----------+---------+-----+-----------+----------+

WITH ranked AS (
    SELECT dept_id, first_name, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
    FROM employees
)
SELECT dept_id, first_name, salary FROM ranked WHERE rn = 1 ORDER BY dept_id;
-- NULL Kiran, 1 Asha, 2 Rahul, 3 Arjun

SELECT order_id, order_date, total_amount,
       LAG(total_amount) OVER w                                AS prev_amount,
       total_amount - LAG(total_amount) OVER w                 AS change_amt,
       DATEDIFF(order_date, LAG(order_date) OVER w)            AS days_since_prev
FROM orders
WINDOW w AS (ORDER BY order_date)
ORDER BY order_date;

+----------+------------+--------------+-------------+------------+-----------------+
| order_id | order_date | total_amount | prev_amount | change_amt | days_since_prev |
+----------+------------+--------------+-------------+------------+-----------------+
|     1001 | 2026-01-05 |     75800.00 |        NULL |       NULL |            NULL |
|     1002 | 2026-01-18 |     12000.00 |    75800.00 |  -63800.00 |              13 |
|     1003 | 2026-02-02 |     18100.00 |    12000.00 |    6100.00 |              15 |
|     1004 | 2026-02-14 |       800.00 |    18100.00 |  -17300.00 |              12 |
|     1005 | 2026-03-01 |      1600.00 |      800.00 |     800.00 |              15 |
|     1006 | 2026-03-10 |     93000.00 |     1600.00 |   91400.00 |               9 |
+----------+------------+--------------+-------------+------------+-----------------+

Expected Result

Each customer's orders numbered and their share of that customer's spend:

SELECT customer_id, order_id, total_amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS seq,
       ROUND(100 * total_amount / SUM(total_amount) OVER (PARTITION BY customer_id), 1) AS pct
FROM orders ORDER BY customer_id, seq;

+-------------+----------+--------------+-----+-------+
| customer_id | order_id | total_amount | seq | pct   |
+-------------+----------+--------------+-----+-------+
|           1 |     1001 |     75800.00 |   1 |  80.7 |
|           1 |     1003 |     18100.00 |   2 |  19.3 |
|           2 |     1002 |     12000.00 |   1 |  11.4 |
|           2 |     1006 |     93000.00 |   2 |  88.6 |
|           3 |     1004 |       800.00 |   1 | 100.0 |
|           4 |     1005 |      1600.00 |   1 | 100.0 |
+-------------+----------+--------------+-----+-------+

Common Mistakes

Window functions in WHERE.

LAST_VALUE without a full frame.

Non-deterministic ROW_NUMBER due to ties.

Recursive CTE column too narrow (anchor type decides).

Using MySQL 5.7 (no CTEs/window functions).

Best Practices

Prefer window functions over self-joins and user variables.

Name windows with WINDOW w AS (...).

Index (partition cols, order cols) for large window queries.

Interview Relevance

Very high: RANK vs DENSE_RANK vs ROW_NUMBER, top-N per group, running totals, recursive CTE for hierarchy, "MySQL version needed for window functions" (8.0).

Practice Questions

Rank products by price within each category.

Compute month-over-month revenue change with LAG.

List the full reporting chain above Priya with a recursive CTE.

Remove duplicate rows keeping the latest per email using ROW_NUMBER.