Window Functions & CTEs (MySQL 8)

Window functions

A window function computes over related rows without collapsing them like GROUP BY. ROW_NUMBER(), RANK() and SUM() OVER are the most common.

SELECT customer_id, amount,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

CTEs with WITH

A common table expression names a subquery so a long query reads top to bottom.

WITH big_orders AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 2000
)
SELECT c.name, b.total
FROM big_orders b
JOIN customers c ON c.id = b.customer_id;

Window functions and CTEs need MySQL 8.0 or newer. Use them instead of self-joins and deeply nested subqueries for ranking and running totals.

Quick check

Quick check: How does a window function differ from GROUP BY?

• It keeps every row while adding a computed value
• It deletes duplicate rows
• It creates a new table
• It only works on text