1.6 — CTEs, Window Functions and Correlated Queries
DP-800 Domain 1 · Skill 1.6
What You Need to Know
Advanced T-SQL on DP-800 means reading and writing common table expressions, window functions and correlated subqueries, and knowing which produces the requested result.
Common table expressions (CTEs)
WITH LatestOrder AS (
SELECT CustomerID, OrderID, OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
FROM dbo.Orders
)
SELECT CustomerID, OrderID, OrderDate FROM LatestOrder WHERE rn = 1;
- A CTE names a subquery for readability. It exists only for the next statement.
- Recursive CTEs walk hierarchies (an org chart, a bill of materials): an anchor member,
UNION ALL, then a recursive member that references the CTE.OPTION (MAXRECURSION n)limits the depth (100 by default).
Window functions
| Family | Functions | Example |
|---|---|---|
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, NTILE |
Latest row per key; top N per group |
| Offset | LAG, LEAD, FIRST_VALUE, LAST_VALUE |
Change from the previous reading |
| Aggregate over a window | SUM, AVG, COUNT ... OVER (...) |
Running totals, moving averages |
SELECT SensorID, ReadingTime, Value,
Value - LAG(Value) OVER (PARTITION BY SensorID ORDER BY ReadingTime) AS Delta,
AVG(Value) OVER (PARTITION BY SensorID ORDER BY ReadingTime
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MovingAvg5
FROM dbo.Readings;
PARTITION BYrestarts the calculation for each group, andORDER BYdefines the sequence.- The frame (
ROWSorRANGE BETWEEN ...) controls which rows aggregate functions include. WithORDER BYand no frame, the default isRANGE UNBOUNDED PRECEDING, which is a running total (ties are included together). RANKleaves gaps after ties (1, 1, 3),DENSE_RANKdoesn't (1, 1, 2), andROW_NUMBERis always unique.- Window functions keep every row.
GROUP BYcollapses rows.
Correlated subqueries
A correlated subquery refers to the outer query's row, so it's evaluated for each outer row (logically):
-- Products priced above their own category's average
SELECT p.ProductID, p.Price
FROM dbo.Product AS p
WHERE p.Price > (SELECT AVG(p2.Price) FROM dbo.Product AS p2 WHERE p2.CategoryID = p.CategoryID);
-- Customers with at least one order this year
SELECT c.CustomerID FROM dbo.Customer AS c
WHERE EXISTS (SELECT 1 FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderDate >= '2026-01-01');
EXISTS/NOT EXISTSwith a correlated subquery is the idiomatic semi-join and anti-join, and it handles NULLs safely.NOT INreturns nothing if the subquery yields a NULL.CROSS APPLY/OUTER APPLYrun a correlated table expression per row, for example the top 3 orders per customer, or a TVF per row.- Many correlated subqueries can be rewritten as window functions or joins, which is often faster.
Key Concept: CTEs make queries readable (and recursive). Window functions calculate across related rows without collapsing them. Correlated subqueries and APPLY evaluate per outer row, and EXISTS is the safe way to test existence.
Exam Traps
- Using
GROUP BYwhen every row must be kept with its rank or running total - Choosing
RANKwhen the requirement says "no gaps" (DENSE_RANK) or "exactly one row" (ROW_NUMBER) - Using
NOT INagainst a column that can contain NULL - Forgetting
PARTITION BY, so a running total spans all sensors
Check your understanding
Practice Scenario
The table dbo.Readings has SensorID, ReadingTime and Value. You need every reading returned, along with the change from that sensor's previous reading. Which expression calculates the change?
Log in to save your progress.
Sources
- WITH common_table_expression, Microsoft Learn
- SELECT - OVER clause, Microsoft Learn
- Correlated subqueries, Microsoft Learn
- FROM clause plus JOIN, APPLY, PIVOT, Microsoft Learn