# Window Functions & CTEs (MySQL 8) — MySQL Fundamentals: SQL, Joins and Transactions

Source: https://www.geekswithgeeks.com/en/mysql/sql-window-functions-cte

> Rank rows and compute running totals with window functions, and structure complex queries with common table expressions.

## 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.

```sql
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.

```sql
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

**Quiz:** How does a window function differ from GROUP BY?

- [x] It keeps every row while adding a computed value
- [ ] It deletes duplicate rows
- [ ] It creates a new table
- [ ] It only works on text

*Answer:* It keeps every row while adding a computed value. Window functions calculate over a window of rows without collapsing them.