Downloads

SQL deep end

Window functions, recursive queries, ROLLUP, deduplication and gaps-and-islands, indexes and execution plans.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Use window functions for running totals, rankings and comparisons with the previous row.
  • Write recursive CTEs for hierarchies and sequences.
  • Use ROLLUP, GROUPING SETS and conditional aggregation for subtotals and pivots.
  • Read an execution plan and find why a query is slow.

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

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.

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

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

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.

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:

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

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.

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

Where next

Take a query like these to Power BI: the same grouping becomes a measure in DAX fundamentals, and the same joins become relationships in Relationships and the data model. To shape data before it loads, see Power Query M.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.