Launch Deal · 50% off 1st month ·

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 BY restarts the calculation for each group, and ORDER BY defines the sequence.
  • The frame (ROWS or RANGE BETWEEN ...) controls which rows aggregate functions include. With ORDER BY and no frame, the default is RANGE UNBOUNDED PRECEDING, which is a running total (ties are included together).
  • RANK leaves gaps after ties (1, 1, 3), DENSE_RANK doesn't (1, 1, 2), and ROW_NUMBER is always unique.
  • Window functions keep every row. GROUP BY collapses 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 EXISTS with a correlated subquery is the idiomatic semi-join and anti-join, and it handles NULLs safely. NOT IN returns nothing if the subquery yields a NULL.
  • CROSS APPLY / OUTER APPLY run 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 BY when every row must be kept with its rank or running total
  • Choosing RANK when the requirement says "no gaps" (DENSE_RANK) or "exactly one row" (ROW_NUMBER)
  • Using NOT IN against 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?

Report issue

Log in to save your progress.

Sources