Window Functions and CTEs in MySQL

Window Functions

MySQL 8 window functions compute aggregate-style values without collapsing rows, so every row keeps its identity while seeing the whole group.

Ranking Sales

SELECT region, salesperson, sales,
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS rnk
FROM sales;

Moving Averages

SELECT created_at, amount,
       AVG(amount) OVER (ORDER BY created_at
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7
FROM payments;

Common Table Expressions

WITH top_regions AS (
  SELECT region, SUM(sales) AS total
  FROM sales GROUP BY region ORDER BY total DESC LIMIT 3
)
SELECT region, total FROM top_regions;

Key Points

• Window functions keep all rows while computing per-group values.
• PARTITION BY resets the window for each group.
• ROWS BETWEEN defines sliding frames.
• CTEs make multi-step queries readable.