What are window functions, and give an example where they help.

Summary

Window functions (MySQL 8.0+) compute values across a set of rows related to the current row without collapsing them like GROUP BY: running totals, rankings, moving averages, previous/next values (LAG, LEAD).

Longer Answer

Latest order per customer:

SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Other uses: SUM(amount) OVER (ORDER BY day) for a running total, RANK() for leaderboards, LAG(value) OVER (...) for day-over-day change. They replace awkward self-joins and correlated subqueries. Window functions run after WHERE and GROUP BY, so filter on their result in an outer query or a CTE.

Key Points

• Aggregate without collapsing rows.
• ROW_NUMBER / RANK / LAG / LEAD / running sums.
• Filter results in an outer query.
• Available from MySQL 8.0.