Thu 8 October 2026 · 22 queries · run on AdventureWorks2022
Goal: rank, number, total and compare rows without GROUP BY collapsing them. The single biggest jump from "knows SQL" to "writes reports".
Read: Microsoft Learn: "OVER clause (Transact-SQL)" and "Ranking functions".
The shape to memorise:FUNCTION() OVER (PARTITION BY group ORDER BY sort). PARTITION BY is like GROUP BY but keeps every row. ORDER BY inside OVER decides the sequence for running calculations.
SELECT Name, ListPrice, ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS Rn
FROM Production.Product WHERE ListPrice > 0;
What this query does
A window function calculates a value for every row while looking at other rows, and unlike GROUP BY it keeps every row. ROW_NUMBER() gives 1, 2, 3... in the order set by ORDER BY inside OVER (most expensive first). All 304 priced products come back, each with its number. The five Road-150 bikes share the top price, so which one gets 1 is arbitrary.
Result in SSMS: 304 rows
#2
2. Restart numbering per colour
SELECT Name, Color, ListPrice,
ROW_NUMBER() OVER (PARTITION BY Color ORDER BY ListPrice DESC) AS RnInColour
FROM Production.Product WHERE ListPrice > 0;
What this query does
PARTITION BY Color splits the rows into one window per colour, and ROW_NUMBER restarts at 1 in each. Still 304 rows. NULL colours form their own partition and come first, which is why the screenshot starts with products that have no colour.
📍 PARTITION BY is like GROUP BY that keeps every row: the numbering starts again at 1 for each colour.
Result in SSMS: 304 rows
#3
3. The three ranking functions side by side. Note how ties behave.
SELECT Name, ListPrice,
ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS RowNum,
RANK() OVER (ORDER BY ListPrice DESC) AS Rnk,
DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS DenseRnk
FROM Production.Product WHERE ListPrice > 0;
What this query does
ROW_NUMBER never repeats: ties get different numbers. RANK gives ties the same number and then skips (1, 1, 1, 1, 1, 6), like sports rankings. DENSE_RANK gives ties the same number without skipping (1, 1, 1, 1, 1, 2). The five Road-150 bikes at 3,578.27 all rank 1; the next product, at 3,399.99, is RANK 6 but DENSE_RANK 2. Use DENSE_RANK for 'the second-highest price', RANK for 'positions in a league table'.
📍 Five bikes tie at the top. The sixth product gets ROW_NUMBER 6, RANK 6 (skips 2-5) and DENSE_RANK 2 (no gaps).
Result in SSMS: 304 rows
#4
4. Top 3 products per subcategory. The pattern you'll write weekly.
WITH Ranked AS (
SELECT p.Name, s.Name AS Subcategory, p.ListPrice,
ROW_NUMBER() OVER (PARTITION BY s.Name ORDER BY p.ListPrice DESC) AS rn
FROM Production.Product p
JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
)
SELECT * FROM Ranked WHERE rn <= 3 ORDER BY Subcategory, rn;
What this query does
The top-N-per-group pattern: number the rows within each group in a CTE, then keep rn <= 3 outside it. The filter has to be outside because a window function cannot be used in WHERE (see the stretch error below). 90 rows: up to 3 per subcategory, fewer where a subcategory has fewer products (Bike Racks has only 1).
Result in SSMS: 90 rows
#5
5. Running total of revenue by month
WITH Monthly AS (
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader GROUP BY FORMAT(OrderDate, 'yyyy-MM')
)
SELECT YearMonth, Revenue, SUM(Revenue) OVER (ORDER BY YearMonth) AS RunningTotal
FROM Monthly ORDER BY YearMonth;
What this query does
This is the query exactly as written in the plan, and it fails. SUM() OVER (ORDER BY ...) without a frame uses the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. A RANGE frame can only sort by values up to 900 bytes, and FORMAT() returns nvarchar(4000), which counts as 8,000 bytes. Two fixes follow.
📍 The plan's query fails with error 8729. FORMAT returns nvarchar(4000), too wide for the default RANGE frame.
Result in SSMS: Error (expected): ORDER BY list of RANGE window frame has total size of 8000 bytes. Largest size supported is 900 bytes.
#6
5. Running total of revenue by month (continued)
-- Fix 1: say ROWS explicitly
WITH Monthly AS (
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader GROUP BY FORMAT(OrderDate, 'yyyy-MM')
)
SELECT YearMonth, Revenue,
SUM(Revenue) OVER (ORDER BY YearMonth ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM Monthly ORDER BY YearMonth;
What this query does
ROWS UNBOUNDED PRECEDING means 'from the first row up to this one', counted by row position, which has no size limit. The running total climbs month by month and the last row equals the full 123.2 million. Writing the frame explicitly is a good habit anyway: ROWS is faster than RANGE, and RANGE treats tied ORDER BY values as one group, which gives surprising totals.
📍 Good habit: always write ROWS UNBOUNDED PRECEDING for running totals. It is also faster than the default RANGE.
Result in SSMS: 38 rows
#7
5. Running total of revenue by month (continued)
-- Fix 2: use a short fixed-length label instead of FORMAT
WITH Monthly AS (
SELECT CONVERT(char(7), OrderDate, 120) AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader GROUP BY CONVERT(char(7), OrderDate, 120)
)
SELECT YearMonth, Revenue, SUM(Revenue) OVER (ORDER BY YearMonth) AS RunningTotal
FROM Monthly ORDER BY YearMonth;
What this query does
CONVERT(char(7), date, 120) takes the first 7 characters of the 'yyyy-mm-dd hh:mi:ss' format, giving '2013-07' as a 7-byte value. That fits inside the 900-byte limit, so the default frame works. It is also much faster than FORMAT on large tables.
Result in SSMS: 38 rows
#8
6. Each month compared to the previous month
WITH Monthly AS (
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader GROUP BY FORMAT(OrderDate, 'yyyy-MM')
)
SELECT YearMonth, Revenue,
LAG(Revenue) OVER (ORDER BY YearMonth) AS PrevMonth,
Revenue - LAG(Revenue) OVER (ORDER BY YearMonth) AS Change,
LEAD(Revenue) OVER (ORDER BY YearMonth) AS NextMonth
FROM Monthly ORDER BY YearMonth;
What this query does
LAG reads a value from the previous row, LEAD from the next row, in the ORDER BY sequence. Revenue - LAG(Revenue) is the month-on-month change. The first month has no previous row, so PrevMonth and Change are NULL; the last month has NULL NextMonth. LAG and LEAD do not use a frame, so FORMAT works here without the error from drill 5.
Result in SSMS: 38 rows
#9
7. Aggregate beside the row, no GROUP BY
SELECT p.Name, p.ProductSubcategoryID, p.ListPrice,
AVG(p.ListPrice) OVER (PARTITION BY p.ProductSubcategoryID) AS SubcatAvg,
p.ListPrice - AVG(p.ListPrice) OVER (PARTITION BY p.ProductSubcategoryID) AS DiffFromAvg
FROM Production.Product p WHERE p.ListPrice > 0;
What this query does
Any aggregate becomes a window function when you add OVER. AVG() OVER (PARTITION BY subcategory) puts the subcategory average on every product row, so each product can be compared with its own group in one query. With GROUP BY you would need a join or subquery to get the same result (Day 7, Q19).
Result in SSMS: 304 rows
#10
8. Percent of total
SELECT t.Name AS Territory, SUM(h.TotalDue) AS Revenue,
SUM(h.TotalDue) * 100.0 / SUM(SUM(h.TotalDue)) OVER () AS PctOfTotal
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
GROUP BY t.Name ORDER BY Revenue DESC;
What this query does
Window functions run after GROUP BY, so they can work on the grouped results. The inner SUM(h.TotalDue) is each territory's revenue; SUM(...) OVER () with empty brackets adds them across every row, giving the grand total. Dividing gives each territory's share: Southwest has 22.0%. Multiplying by 100.0 (not 100) keeps the decimals.
📍 SUM(SUM(x)) OVER (): the inner SUM is per territory, the outer SUM adds those up across all rows.
Result in SSMS: 10 rows
#11
9. NTILE. Split customers into 4 revenue quartiles.
SELECT CustomerID, SUM(TotalDue) AS Revenue,
NTILE(4) OVER (ORDER BY SUM(TotalDue) DESC) AS Quartile
FROM Sales.SalesOrderHeader GROUP BY CustomerID;
What this query does
NTILE(4) sorts the rows and deals them into 4 groups of (almost) equal size: 1 is the top quarter of customers by revenue, 4 the bottom. 19,119 customers, so each quartile holds about 4,780.
Result in SSMS: 19,119 rows
#12
10. FIRST_VALUE. Each order with the customer's first order date.
SELECT CustomerID, SalesOrderID, OrderDate,
FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS FirstOrder,
DATEDIFF(day, FIRST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate),
OrderDate) AS DaysSinceFirst
FROM Sales.SalesOrderHeader;
What this query does
FIRST_VALUE returns the first value in each customer's window, here their first order date, on every one of their orders. DATEDIFF then shows how long after their first order each later order came: customer 11000 ordered again 730 days later. LAST_VALUE is the trap: with the default frame the window ends at the current row, so it returns the current row's value unless you extend the frame to UNBOUNDED FOLLOWING.
📍 LAST_VALUE needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, otherwise it just returns the current row.
Result in SSMS: 31,465 rows
Stretch
#13
Moving average, last 3 months
WITH Monthly AS (
SELECT FORMAT(OrderDate, 'yyyy-MM') AS YearMonth, SUM(TotalDue) AS Revenue
FROM Sales.SalesOrderHeader GROUP BY FORMAT(OrderDate, 'yyyy-MM')
)
SELECT YearMonth, Revenue,
AVG(Revenue) OVER (ORDER BY YearMonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3
FROM Monthly;
What this query does
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a sliding frame of 3 rows: this month and the two before. The average smooths out spiky months. The first two months average over only 1 and 2 months, because there are no earlier rows. This works with FORMAT because it uses a ROWS frame.
Result in SSMS: 38 rows
#14
Remove duplicates with ROW_NUMBER. People sharing first and last name.
WITH Dupes AS (
SELECT BusinessEntityID, FirstName, LastName,
ROW_NUMBER() OVER (PARTITION BY FirstName, LastName ORDER BY BusinessEntityID) AS rn
FROM Person.Person
)
SELECT * FROM Dupes WHERE rn > 1;
What this query does
The standard de-duplication pattern: number the rows within each group of duplicates, keep rn = 1 as the original, and everything with rn > 1 is a copy. 456 people share a first and last name with someone earlier in the table (Laura Norman appears 5 times). Changing SELECT to DELETE in the outer query is how duplicates are removed from a real table. This is also why Day 7 Q17 said to group by CustomerID, not by name.
Result in SSMS: 456 rows
#15
Gaps: customers whose gap between consecutive orders exceeded 365 days
WITH Gaps AS (
SELECT CustomerID, OrderDate,
LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PrevOrder
FROM Sales.SalesOrderHeader
)
SELECT CustomerID, PrevOrder, OrderDate, DATEDIFF(day, PrevOrder, OrderDate) AS GapDays
FROM Gaps WHERE DATEDIFF(day, PrevOrder, OrderDate) > 365;
What this query does
LAG partitioned by customer puts each customer's previous order date beside each order; the outer query keeps gaps longer than a year. 3,486 rows, one for each of 3,486 customers. Most are 2011 customers who came back in 2013. The first order of each customer has a NULL PrevOrder, and DATEDIFF with NULL is NULL, so those rows drop out automatically.
Result in SSMS: 3,486 rows
#16
Window function in WHERE fails. Read the error, then wrap it in a CTE.
SELECT Name, ROW_NUMBER() OVER (ORDER BY ListPrice) AS rn
FROM Production.Product
WHERE ROW_NUMBER() OVER (ORDER BY ListPrice) <= 5;
What this query does
Error 4108: windowed functions can only appear in the SELECT or ORDER BY clauses. The logical order from Day 6 explains it: window functions are calculated at the SELECT step, after WHERE has already run, so WHERE cannot see them.
Result in SSMS: Error (expected): Windowed functions can only appear in the SELECT or ORDER BY clauses.
#17
Window function in WHERE fails. Read the error, then wrap it in a CTE. (continued)
WITH Numbered AS (
SELECT Name, ListPrice, ROW_NUMBER() OVER (ORDER BY ListPrice) AS rn
FROM Production.Product
)
SELECT * FROM Numbered WHERE rn <= 5;
What this query does
The fix: calculate the row number inside a CTE, where it becomes an ordinary column, then filter it in the outer query. This is the same shape as drill 4. 5 rows: five of the 0-priced components.
Result in SSMS: 5 rows
Self-test
#18
Q1. The most expensive product in each colour
WITH Ranked AS (
SELECT Name, Color, ListPrice,
RANK() OVER (PARTITION BY Color ORDER BY ListPrice DESC) AS rk
FROM Production.Product
WHERE ListPrice > 0 AND Color IS NOT NULL
)
SELECT Color, Name, ListPrice FROM Ranked WHERE rk = 1 ORDER BY Color, Name;
What this query does
Rank products within each colour, highest price first, and keep rank 1. RANK keeps every product that ties for first place, which is the honest answer: 30 rows for 9 colours, because the four Mountain-100 Black sizes all cost 3,374.99, for example. ROW_NUMBER would silently pick just one of them.
📍 RANK, not ROW_NUMBER: if several products tie for the top price, all of them are the 'most expensive'.
Result in SSMS: 30 rows
#19
Q2. Cumulative order count per territory by month
WITH Monthly AS (
SELECT t.Name AS Territory, FORMAT(h.OrderDate, 'yyyy-MM') AS YearMonth, COUNT(*) AS Orders
FROM Sales.SalesOrderHeader h
JOIN Sales.SalesTerritory t ON h.TerritoryID = t.TerritoryID
GROUP BY t.Name, FORMAT(h.OrderDate, 'yyyy-MM')
)
SELECT Territory, YearMonth, Orders,
SUM(Orders) OVER (PARTITION BY Territory ORDER BY YearMonth
ROWS UNBOUNDED PRECEDING) AS CumulativeOrders
FROM Monthly ORDER BY Territory, YearMonth;
What this query does
First count orders per territory per month in a CTE, then run a total that restarts for each territory (PARTITION BY Territory). ROWS UNBOUNDED PRECEDING avoids the drill 5 error. 364 territory-months; each territory's last row equals its total orders, so Australia ends at 6,843.
Result in SSMS: 364 rows
#20
Q3. Each order's TotalDue compared to the same customer's previous order
SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
LAG(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID) AS PrevTotal,
TotalDue - LAG(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID) AS Change
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate, SalesOrderID;
What this query does
LAG partitioned by customer looks back to that customer's previous order only. SalesOrderID is added to the ORDER BY so two orders on the same day still have a fixed sequence. A customer's first order has NULL PrevTotal. Customer 11000 spent 1,169.11 less on their second order than their first.
Result in SSMS: 31,465 rows
#21
Q4. The second-highest salesperson by revenue, using DENSE_RANK
WITH SalesByPerson AS (
SELECT h.SalesPersonID, pp.FirstName + ' ' + pp.LastName AS SalesPerson,
SUM(h.TotalDue) AS Revenue,
DENSE_RANK() OVER (ORDER BY SUM(h.TotalDue) DESC) AS dr
FROM Sales.SalesOrderHeader h
JOIN Person.Person pp ON h.SalesPersonID = pp.BusinessEntityID
GROUP BY h.SalesPersonID, pp.FirstName, pp.LastName
)
SELECT SalesPersonID, SalesPerson, Revenue FROM SalesByPerson WHERE dr = 2;
What this query does
Group orders by salesperson, rank the totals with DENSE_RANK, and keep rank 2. The INNER JOIN to Person drops online orders, which have no salesperson. Jillian Carson is second with 11.34 million, behind Linda Mitchell's 11.70 million. DENSE_RANK means that if two people tied for first, the next person would still be rank 2.
Result in SSMS: 1 row
#22
Q5. Customers in the top 10 percent by revenue, using NTILE(10)
WITH Deciles AS (
SELECT CustomerID, SUM(TotalDue) AS Revenue,
NTILE(10) OVER (ORDER BY SUM(TotalDue) DESC) AS Decile
FROM Sales.SalesOrderHeader GROUP BY CustomerID
)
SELECT CustomerID, Revenue FROM Deciles WHERE Decile = 1 ORDER BY Revenue DESC;
What this query does
NTILE(10) deals the 19,119 customers into 10 groups by revenue; group 1 is the top 10%. 1,912 customers (19,119 / 10 rounded up, because NTILE gives the extra rows to the first groups).
Result in SSMS: 1,912 rows
Lesson from drill 5: a running total with SUM() OVER (ORDER BY …) and no frame uses RANGE by default, which fails when sorting by a long text value such as FORMAT(). Always write ROWS UNBOUNDED PRECEDING for running totals: it avoids the error and is faster.