Welcome back. In the last lesson you learned CTEs—how to write readable, staged queries.
Window functions are the next step because they let you calculate values “across rows”
without collapsing your result set the way GROUP BY does.
This is one of the most important skills for DBAs because real systems constantly require:
“Top 1 per customer,” “latest event per server,” “rank the slowest queries,” “running totals,”
“percent of total,” “detect duplicates,” and “find gaps.”
Lesson description: You will learn what window functions are, how the OVER clause works,
how PARTITION BY and ORDER BY change the calculation, and how to use
ROW_NUMBER(), RANK(), and SUM() OVER for job-ready reporting and operational DBA scripts.
You will also learn common mistakes (wrong partitions, wrong ordering, hidden duplicates) and safe validation methods.
A window function returns a value for each row, but it can “look at” other rows in a defined window.
The window is defined by the OVER (...) clause.
The key difference:
This is why window functions are perfect for “rank within group” and “running total” problems.
Most window functions follow this shape:
FunctionName() OVER
(
PARTITION BY SomeGroupColumns
ORDER BY SomeOrderColumns
)
PARTITION BY breaks rows into independent groups (partitions).
The calculation restarts inside each partition.
ORDER BY defines the row ordering within each partition.
This is required for ranking functions and running totals.
DBA rule: Before writing the window function, say out loud:
“What is my partition (group), and what is my order (sequence)?”
ROW_NUMBER() assigns a unique sequence number to rows within a partition.
It is commonly used for:
This query returns one row per customer: their most recent order.
;WITH RankedOrders AS
(
SELECT
soh.CustomerID,
soh.SalesOrderID,
soh.OrderDate,
soh.TotalDue,
ROW_NUMBER() OVER
(
PARTITION BY soh.CustomerID
ORDER BY soh.OrderDate DESC, soh.SalesOrderID DESC
) AS rn
FROM Sales.SalesOrderHeader AS soh
)
SELECT
ro.CustomerID,
ro.SalesOrderID,
ro.OrderDate,
ro.TotalDue
FROM RankedOrders AS ro
WHERE ro.rn = 1
ORDER BY ro.OrderDate DESC;
DBA safety detail: The tie-breaker SalesOrderID DESC makes the ordering deterministic
when two orders have the same OrderDate.
If a system has duplicate emails (should be unique), you can flag them.
This is a common DBA data-quality check.
;WITH EmailRanks AS
(
SELECT
c.CustomerID,
c.EmailAddress,
ROW_NUMBER() OVER
(
PARTITION BY c.EmailAddress
ORDER BY c.CustomerID
) AS rn
FROM dbo.Customer AS c
WHERE c.EmailAddress IS NOT NULL
)
SELECT *
FROM EmailRanks
WHERE rn > 1
ORDER BY EmailAddress, rn;
Job-ready point: rn > 1 are “extra duplicates.” rn = 1 is the “keeper row.”
In production, you would also identify which row should truly be kept based on business rules.
RANK() assigns a rank within a partition based on ORDER BY,
but unlike ROW_NUMBER, it gives the same rank to ties and then skips ranks.
Example: values 100, 90, 90, 80 produce ranks 1, 2, 2, 4.
We first aggregate spend per customer, then rank customers by TotalSpent.
This is a classic “report + ranking” scenario.
;WITH CustomerSpend AS
(
SELECT
soh.CustomerID,
SUM(soh.TotalDue) AS TotalSpent
FROM Sales.SalesOrderHeader AS soh
GROUP BY soh.CustomerID
),
RankedCustomers AS
(
SELECT
cs.CustomerID,
cs.TotalSpent,
RANK() OVER (ORDER BY cs.TotalSpent DESC) AS SpendRank
FROM CustomerSpend AS cs
)
SELECT TOP (50)
rc.CustomerID,
rc.TotalSpent,
rc.SpendRank
FROM RankedCustomers AS rc
ORDER BY rc.SpendRank, rc.CustomerID;
DBA note: If you need unique ranks (no ties), use ROW_NUMBER instead.
If you want ties to share rank, use RANK.
SUM() OVER is the foundation for:
We first generate daily totals, then compute a running total over time.
This is exactly how DBAs build dashboards and trend reports.
;WITH DailySales AS
(
SELECT
CAST(soh.OrderDate AS date) AS OrderDate,
SUM(soh.TotalDue) AS DailyRevenue
FROM Sales.SalesOrderHeader AS soh
GROUP BY CAST(soh.OrderDate AS date)
)
SELECT
ds.OrderDate,
ds.DailyRevenue,
SUM(ds.DailyRevenue) OVER (ORDER BY ds.OrderDate ASC) AS RunningRevenue
FROM DailySales AS ds
ORDER BY ds.OrderDate ASC;
DBA skill: You created a trend report with a running total—very common in business reporting.
This shows each category’s share of total revenue.
Window functions let you compute the grand total without a second query.
;WITH CategoryRevenue AS
(
SELECT
pc.ProductCategoryID,
pc.Name AS CategoryName,
SUM(sod.LineTotal) AS Revenue
FROM Sales.SalesOrderDetail AS sod
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
JOIN Production.ProductSubcategory AS psc
ON p.ProductSubcategoryID = psc.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
ON psc.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name
)
SELECT
cr.CategoryName,
cr.Revenue,
SUM(cr.Revenue) OVER () AS GrandTotalRevenue,
CAST((cr.Revenue / NULLIF(SUM(cr.Revenue) OVER (), 0)) * 100.0 AS DECIMAL(10,2)) AS PctOfTotal
FROM CategoryRevenue AS cr
ORDER BY cr.Revenue DESC;
DBA safety detail: NULLIF prevents divide-by-zero (rare here, but professional habit).
This is one of the most frequently requested patterns in the real world:
“Top 3 products per category,” “Top 5 customers per territory,” etc.
The standard approach is:
;WITH ProductRevenue AS
(
SELECT
pc.Name AS CategoryName,
p.ProductID,
p.Name AS ProductName,
SUM(sod.LineTotal) AS Revenue
FROM Sales.SalesOrderDetail AS sod
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
JOIN Production.ProductSubcategory AS psc
ON p.ProductSubcategoryID = psc.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
ON psc.ProductCategoryID = pc.ProductCategoryID
GROUP BY pc.Name, p.ProductID, p.Name
),
RankedProducts AS
(
SELECT
pr.CategoryName,
pr.ProductID,
pr.ProductName,
pr.Revenue,
ROW_NUMBER() OVER
(
PARTITION BY pr.CategoryName
ORDER BY pr.Revenue DESC, pr.ProductID ASC
) AS rn
FROM ProductRevenue AS pr
)
SELECT
rp.CategoryName,
rp.ProductID,
rp.ProductName,
rp.Revenue
FROM RankedProducts AS rp
WHERE rp.rn <= 3
ORDER BY rp.CategoryName, rp.Revenue DESC;
DBA check: Confirm that each category shows at most 3 products.
If you forget PARTITION BY when you need it, you will rank globally—not per group.
Always confirm your output grain.
If your ORDER BY column has ties and you do not add a tie-breaker, results can change between runs.
DBAs add stable tie-breakers (like IDs).
Window totals keep rows. GROUP BY collapses rows.
Always choose based on whether you need row-level detail preserved.
Use NULLIF and explicit casting for stable, predictable outputs.
Do these exercises in AdventureWorks and save them in your script pack:
ROW_NUMBER() is best for unique sequencing, de-duplication, and “pick one row per group.”RANK() is best when ties should share rank (business ranking).SUM() OVER enables running totals and percent-of-total calculations.Next, you will learn how DBAs encode business rules safely using CASE expressions,
including status mapping, tiering, anomaly flags, and “data quality score” patterns.
Not a member yet? Register now
Are you a member? Login now