<!-- https://learnsql.cpetoday.com/analyze/sql-deep-end -->
# SQL deep end: windows, recursion, plans and patterns

This guide is for when you know `SELECT`, `JOIN` and `GROUP BY` and want the techniques that separate quick queries from reliable ones. It assumes [SQL essentials](/analyze/sql-essentials). Examples use `classicmodels` on SQL Server; the notes mark where MariaDB differs.

## Window functions

A **window function** calculates across a set of rows **without collapsing them**, so you keep the detail and add a number next to it.

```sql
SELECT orderNumber, customerNumber, orderDate,
       ROW_NUMBER() OVER (PARTITION BY customerNumber ORDER BY orderDate) AS nth_order
FROM classicmodels.orders;
```

`OVER (...)` defines the window: `PARTITION BY` splits rows into groups (like `GROUP BY`, but rows stay), and `ORDER BY` orders within each group.

### The four ranking functions

| Function | Ties | Example values for 10, 10, 8 |
|---|---|---|
| `ROW_NUMBER()` | Always unique | 1, 2, 3 |
| `RANK()` | Same rank, then a gap | 1, 1, 3 |
| `DENSE_RANK()` | Same rank, no gap | 1, 1, 2 |
| `NTILE(4)` | Splits rows into 4 buckets | quartiles |

**Top 1 per group** (the best order per customer): rank inside a CTE, filter outside. A window function cannot be used in `WHERE`.

```sql
WITH ranked AS (
    SELECT o.customerNumber, o.orderNumber,
           SUM(d.quantityOrdered * d.priceEach) AS order_total,
           ROW_NUMBER() OVER (PARTITION BY o.customerNumber
                              ORDER BY SUM(d.quantityOrdered * d.priceEach) DESC) AS rn
    FROM classicmodels.orders o
    JOIN classicmodels.orderdetails d ON d.orderNumber = o.orderNumber
    GROUP BY o.customerNumber, o.orderNumber
)
SELECT customerNumber, orderNumber, order_total
FROM ranked
WHERE rn = 1;
```

### Running totals and moving averages

```sql
SELECT paymentDate, amount,
       SUM(amount) OVER (ORDER BY paymentDate, checkNumber
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY paymentDate, checkNumber
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7
FROM classicmodels.payments;
```

Always write the **frame** (`ROWS BETWEEN ...`) yourself. The default frame with an `ORDER BY` is `RANGE`, which treats rows with equal sort values as one group and can surprise you. Add a unique tie-breaker (`checkNumber`) to the order.

### Compare to the previous or next row

```sql
SELECT customerNumber, paymentDate, amount,
       LAG(amount)  OVER (PARTITION BY customerNumber ORDER BY paymentDate) AS prev_amount,
       amount - LAG(amount, 1, 0) OVER (PARTITION BY customerNumber ORDER BY paymentDate) AS change
FROM classicmodels.payments;
```

`LAG(x, n, default)` looks `n` rows back; `LEAD` looks forward. `FIRST_VALUE` and `LAST_VALUE` return the first or last value of the window. For `LAST_VALUE` you must widen the frame to `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`, or it returns the current row.

**Percent of total** is a window over everything: `amount * 1.0 / SUM(amount) OVER ()`.

MariaDB supports window functions from version 10.2 with the same syntax.

## Recursive CTEs: trees and sequences

`employees.reportsTo` points at another employee. A **recursive CTE** walks that tree.

```sql
WITH org AS (
    SELECT employeeNumber, lastName, reportsTo, 0 AS depth,
           CAST(lastName AS varchar(400)) AS path
    FROM classicmodels.employees
    WHERE reportsTo IS NULL
  UNION ALL
    SELECT e.employeeNumber, e.lastName, e.reportsTo, o.depth + 1,
           CAST(o.path + ' > ' + e.lastName AS varchar(400))
    FROM classicmodels.employees e
    JOIN org o ON e.reportsTo = o.employeeNumber
)
SELECT depth, path FROM org ORDER BY path;
```

The first `SELECT` is the **anchor** (the top of the tree). The second joins back to `org` itself to find the next level, and repeats until nothing new appears. SQL Server stops at 100 levels unless you add `OPTION (MAXRECURSION 0)`. On MariaDB write `WITH RECURSIVE org AS (...)` and use `CONCAT` for the path.

A recursive CTE also generates a calendar: start from one date and add a day until you reach the end date.

## Reshape: pivot with conditional aggregation

Turning rows into columns is the same everywhere with `CASE` inside an aggregate, and easy to read:

```sql
SELECT p.productLine,
       SUM(CASE WHEN YEAR(o.orderDate) = 2025 THEN d.quantityOrdered * d.priceEach ELSE 0 END) AS sales_2025,
       SUM(CASE WHEN YEAR(o.orderDate) = 2026 THEN d.quantityOrdered * d.priceEach ELSE 0 END) AS sales_2026
FROM classicmodels.orders o
JOIN classicmodels.orderdetails d ON d.orderNumber = o.orderNumber
JOIN classicmodels.products p     ON p.productCode = d.productCode
GROUP BY p.productLine;
```

SQL Server also has `PIVOT` and `UNPIVOT`, and `CROSS APPLY (VALUES ...)` to unpivot columns into rows.

## Totals and subtotals: ROLLUP and GROUPING SETS

```sql
-- SQL Server
SELECT p.productLine, YEAR(o.orderDate) AS yr, SUM(d.quantityOrdered * d.priceEach) AS sales
FROM classicmodels.orders o
JOIN classicmodels.orderdetails d ON d.orderNumber = o.orderNumber
JOIN classicmodels.products p     ON p.productCode = d.productCode
GROUP BY ROLLUP (p.productLine, YEAR(o.orderDate));
```

`ROLLUP(a, b)` returns detail rows, a subtotal for each `a`, and a grand total. `GROUPING SETS` lets you list the exact combinations. Use `GROUPING(col)` to tell a subtotal's `NULL` from a real `NULL`. MariaDB writes it `GROUP BY a, b WITH ROLLUP`.

## Patterns worth knowing

### Deduplicate: keep one row per key

```sql
WITH d AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customerName ORDER BY customerNumber) AS rn
    FROM classicmodels.customers
)
SELECT * FROM d WHERE rn = 1;
```

Changing the `ORDER BY` decides **which** duplicate survives (newest, biggest, first).

### Latest row per group

`ROW_NUMBER() ... ORDER BY date DESC` then `rn = 1`, as above. The alternative, `WHERE date = (SELECT MAX(date) ...)` with a correlated subquery, returns ties as extra rows.

### Gaps and islands: find runs

To group consecutive numbers or dates, subtract a row number: values in the same run share the same difference.

```sql
WITH n AS (
    SELECT DISTINCT CAST(orderDate AS date) AS d FROM classicmodels.orders
),
g AS (
    SELECT d, DATEADD(day, -ROW_NUMBER() OVER (ORDER BY d), d) AS grp FROM n
)
SELECT MIN(d) AS run_start, MAX(d) AS run_end, COUNT(*) AS days
FROM g GROUP BY grp ORDER BY run_start;
```

### Top N per group without a window

`SELECT TOP (3) ... CROSS APPLY` works on SQL Server: for each parent row, run a small ordered subquery. It reads well when the parent list is small.

### Always-correct "is there a match"

Prefer `EXISTS` over `IN` for large or nullable lists, and `NOT EXISTS` over `NOT IN`.

## Performance: why a query is slow

Most slow queries are slow for one of four reasons.

1. **Too many rows are read.** The database scanned a whole table because nothing helped it find the few rows you asked for.
2. **A function hides the column.** `WHERE YEAR(orderDate) = 2026` cannot use an index on `orderDate`. Write a range instead: `WHERE orderDate >= '2026-01-01' AND orderDate < '2027-01-01'`. A condition that can use an index is called **sargable**.
3. **A join multiplies rows** before you aggregate. Aggregate the many side first (in a CTE), then join.
4. **Sorting or grouping huge sets** that did not need to be that large. Filter and project early.

### Indexes in one minute

An index is a sorted copy of one or more columns with pointers to the rows, like a book's index. It speeds up lookups, joins and sorts on those columns and slows writes slightly. A **composite** index on `(customerNumber, orderDate)` helps filters on `customerNumber` and on `customerNumber` plus `orderDate`, not on `orderDate` alone. The sample databases are read-only here, so you cannot create indexes, but you can read which ones exist and recognize what a plan does with them.

### Read an execution plan

In SQL Server Management Studio or Azure Data Studio choose **Include Actual Execution Plan**. In MariaDB use `EXPLAIN`. Look for:

| In the plan | Means | Often fixed by |
|---|---|---|
| Table or index **scan** on a big table | Whole table read | A filter that can use an index, a better index |
| **Key lookup** repeated many times | Index found rows, then went back for more columns | Include the columns in the index, or select fewer |
| **Hash match** or **sort** with a big memory grant | Large intermediate sets | Filter earlier, aggregate before joining |
| Estimated rows far from actual rows | Stale statistics or a hard-to-estimate predicate | Update statistics, simplify the predicate |

Use `SET STATISTICS IO, TIME ON` on SQL Server to see logical reads and time per query.

> **Tip.** Fewer **logical reads** is the most reliable sign of a better query.

## Transactions and isolation (just enough)

Each statement is atomic. A **transaction** groups statements so they all happen or none do. Readers can see unfinished changes at low isolation levels. On SQL Server, `READ COMMITTED SNAPSHOT` avoids readers waiting on writers. For reporting on a live system, read from a replica or a snapshot rather than adding `NOLOCK`, which can return duplicate or missing rows.

## Habits of reliable SQL

- **Alias every table** and qualify every column.
- **Name the key** in joins; never join "on whatever looks right".
- **Check counts** at each step: rows in, rows out, distinct keys.
- **Format for reading**: one clause per line, one join per line.
- **Put business rules in one place** (a view or a CTE), then reuse it.
- **Test on a small slice first**, then widen.

## Where next

Take a query like these to Power BI: the same grouping becomes a measure in [DAX fundamentals](/analyze/fundamentals), and the same joins become relationships in [Relationships and the data model](/analyze/relationships). To shape data before it loads, see [Power Query M](/analyze/power-query-m-basics).
