Day 5: Window functions, part 1

A. Lesson overview

Activity breakdown

Business scenario. It is bonus season. HR needs salary rankings with fair treatment of ties. Sales wants league tables: the best products in each category, the top customers in each country, the leading salesperson in each shop. Data engineering needs to clean duplicate records in the staging tables before loading them. All of these are “rank within a group” problems — exactly what window functions solve, cleanly and efficiently.

B. Theory explained clearly

Part 1 — What a window is

B1. Window functions versus GROUP BY

GROUP BY collapses each group to one row. A window function computes a value from a set of rows — the window — but returns one value for every row, so you keep the detail and gain the calculation.

USE RetailLab;
GO

SELECT  EmployeeID,
        FirstName,
        DepartmentID,
        Salary,
        AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary   -- same value on every row of the department
FROM    HR.Employee
ORDER BY DepartmentID, EmployeeID;

All 16 employees are returned, each next to their department’s average. Compare Day 3, where you needed a correlated subquery for the same thing.

B2. The OVER clause

function_name ( arguments ) OVER ( [PARTITION BY expr, …]  [ORDER BY expr [ASC|DESC], …] )

The ORDER BY inside OVER controls the calculation only. It does not sort the output: you still need a final ORDER BY for that.

B3. Where window functions fit in logical processing order

Consequences:

• Window functions see only rows that survived WHERE, GROUP BY and HAVING.
• Window functions cannot be used in WHERE, GROUP BY or HAVING (error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses.). To filter on a rank, compute it in a CTE or derived table and filter in the outer query — Part 3.
• They can be used in the final ORDER BY.

Part 2 — The four ranking functions

B4. ROW_NUMBER, RANK, DENSE_RANK and NTILE

All four require ORDER BY inside OVER.

Which one should I use?

• “Give every row a unique position” (paging, picking exactly one row per group, de-duplication) → ROW_NUMBER with a unique tie-breaker.
• “Sports-table ranking: two people joint-second, next person fourth” → RANK.
• “Top 3 distinct values, however many rows share them” → DENSE_RANK.
• “Split into quartiles/deciles” → NTILE.

B5. Deterministic ROW_NUMBER

Four pairs of employees share a salary. With ROW_NUMBER() OVER (ORDER BY Salary DESC), the order of Tom Baxter and Aisha Khan (both £72,000) is not defined — SQL Server may number them either way, and the choice can change between executions, servers or after an index change. Add a unique column as the final sort key: ORDER BY Salary DESC, EmployeeID.

The same rule applies to NTILE: when the boundary between two buckets falls inside a group of ties, a unique tie-breaker decides which bucket each tied row lands in.

B6. How NTILE sizes its buckets

NTILE(n) divides the partition’s row count by n. If it doesn’t divide evenly, the first buckets get one extra row each. 35 customers into 4 quartiles → 9, 9, 9, 8. 16 employees into 4 → 4, 4, 4, 4.

NTILE divides by row count, not by value. Quartile 1 is “the top quarter of rows”, not “values above the 75th percentile”. The two can differ considerably when values are skewed or tied.

Part 3 — Filtering on a rank

B7. The top-N-per-group pattern

WITH Ranked AS
(
    SELECT  …,
            ROW_NUMBER() OVER (PARTITION BY GroupColumn ORDER BY Measure DESC, UniqueKey) AS rn
    FROM    …
)
SELECT  …
FROM    Ranked
WHERE   rn <= 3                -- top 3 per group
ORDER BY GroupColumn, rn;

Swap ROW_NUMBER for RANK or DENSE_RANK if tied rows at the boundary should all be included. This pattern replaces the “row with the group’s maximum” correlated subqueries from Day 3, reads the data once and lets you pick exactly one row per group when there are ties.

B8. De-duplication

Duplicate business records — the same person imported twice — are a ranking problem too: partition by the columns that define “the same record”, order by which copy you want to keep, and keep rn = 1.

WITH Numbered AS
(
    SELECT  ImportRowID, Email,
            ROW_NUMBER() OVER (PARTITION BY TRIM(Email) ORDER BY ImportRowID) AS rn
    FROM    Staging.CustomerImport
)
SELECT * FROM Numbered WHERE rn > 1;     -- the copies to discard

• TRIM (SQL Server 2017 and later) removes leading and trailing spaces.
• RetailLab uses a case-insensitive collation, so KOFI.MENSAH@EXAMPLE.COM and kofi.mensah@example.com fall into the same partition without LOWER. On a case-sensitive database you would add LOWER(…).
• All NULL emails form one partition. Decide whether that is right — two rows with no email are not necessarily the same person. Today’s examples flag rather than delete; Day 15 performs the actual DELETE through a CTE.

Part 4 — Windowed aggregates

B9. Aggregates with OVER

SUM, COUNT, AVG, MIN and MAX can all take an OVER clause. Without ORDER BY inside OVER, the window is the whole partition:

SELECT  ProductCode, CategoryID, ListPrice,
        COUNT(*)       OVER (PARTITION BY CategoryID) AS ProductsInCategory,
        MAX(ListPrice) OVER (PARTITION BY CategoryID) AS CategoryMaxPrice,
        CAST(100.0 * ListPrice / SUM(ListPrice) OVER () AS decimal(5,2)) AS PctOfCatalogueValue
FROM    Inventory.Product
ORDER BY CategoryID, ProductCode;

OVER () with empty parentheses means “all rows of the result”. Adding ORDER BY inside an aggregate’s OVER turns it into a running total — Day 6.

B10. Window functions over grouped rows

When a query has GROUP BY, window functions run after grouping, so they operate on the grouped rows. That means you can rank or share aggregates directly:

SELECT  o.CustomerID,
        COUNT(*) AS Orders,
        RANK() OVER (ORDER BY COUNT(*) DESC)                     AS OrderRank,
        CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS decimal(5,2)) AS PctOfAllOrders
FROM    Sales.SalesOrder AS o
WHERE   o.OrderStatus <> 'Cancelled'
GROUP BY o.CustomerID
ORDER BY OrderRank, o.CustomerID;

SUM(COUNT(*)) OVER () looks odd but reads naturally: the inner COUNT(*) is the group aggregate, the outer SUM … OVER () adds those counts across all groups. Any column used inside the window function must be either grouped or wrapped in an aggregate — the golden rule from Day 2 still applies.

Optional syntax (SQL Server 2022 and later, compatibility level 160+): a WINDOW clause can name a window once and reuse it — … OVER w … WINDOW w AS (PARTITION BY DepartmentID ORDER BY Salary DESC). The course writes windows in full so the code also runs on older versions.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03.

Validation. Expected: 16, 12, 15, 10.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM HR.Employee)                        AS Employees,
        (SELECT COUNT(DISTINCT Salary) FROM HR.Employee)          AS DistinctSalaries,
        (SELECT COUNT(*) FROM Staging.CustomerImport)             AS ImportRows,
        (SELECT COUNT(*) FROM Staging.ProductPriceUpdate)         AS PriceUpdateRows;

D. Worked examples

Worked example 1 — Four ranking functions side by side

SELECT  EmployeeID,
        CONCAT_WS(N' ', FirstName, LastName)                         AS EmployeeName,
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC, EmployeeID)         AS RowNum,
        RANK()       OVER (ORDER BY Salary DESC)                     AS RankNo,
        DENSE_RANK() OVER (ORDER BY Salary DESC)                     AS DenseRankNo,
        NTILE(4)     OVER (ORDER BY Salary DESC, EmployeeID)         AS Quartile
FROM    HR.Employee
ORDER BY Salary DESC, EmployeeID;

Expected result: 16 rows.

What to notice

• Tom and Aisha tie: RANK gives both 4 and skips 5; DENSE_RANK gives both 4 and continues with 5.
• NTILE(4) puts Tom in quartile 1 and Aisha in quartile 2 although they earn the same. If HR’s bonus rules depended on quartile, that would be unfair — use a rank-based rule instead, or agree how ties at a boundary are treated.
• The last RANK (15) plus the tie equals the row count (16). The last DENSE_RANK (12) equals the number of distinct salaries.

Worked example 2 — Ranking within each department

SELECT  DepartmentID,
        EmployeeID,
        FirstName,
        Salary,
        RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankInDept
FROM    HR.Employee
ORDER BY DepartmentID, RankInDept, EmployeeID;

Expected result: 16 rows; ranks restart at 1 in each department. Sales (department 2): Raj 1, Liam 2, Sophie and Daniel 3, James 5, Megan and Chen 6, Oliver 8. Warehouse & Logistics (3): Fiona 1, Gareth 2, Hannah and Marek 3.

Worked example 3 — Top 2 customers per country (top-N per group)

WITH CustomerSales AS
(
    SELECT  c.Country,
            o.CustomerID,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS LifetimeNet
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
    JOIN    Sales.Customer       AS c ON c.CustomerID = o.CustomerID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY c.Country, o.CustomerID
),
Ranked AS
(
    SELECT  Country,
            CustomerID,
            LifetimeNet,
            ROW_NUMBER() OVER (PARTITION BY Country ORDER BY LifetimeNet DESC, CustomerID) AS rn,
            COUNT(*)     OVER (PARTITION BY Country)                                       AS CustomersInCountry
    FROM    CustomerSales
)
SELECT  Country, rn AS Position, CustomerID,
        CAST(LifetimeNet AS decimal(12,2)) AS LifetimeNet,
        CustomersInCountry
FROM    Ranked
WHERE   rn <= 2
ORDER BY Country, rn;

Expected result: 12 rows. Countries with one ordering customer (Denmark, Italy, Spain, United States) return one row each.

The INNER JOIN to Sales.Customer is a three-table join: each additional JOIN … ON … adds one table. CustomersInCountry shows a windowed COUNT living alongside a ranking function.

Worked example 4 — Largest order per customer, the clean way

Day 3 Task 4 needed the per-order derived table twice. With ROW_NUMBER:

WITH OrderTotals AS
(
    SELECT  o.OrderID, o.CustomerID, o.OrderDate,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY o.OrderID, o.CustomerID, o.OrderDate
),
Ranked AS
(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY CustomerID
                               ORDER BY OrderNet DESC, OrderDate DESC, OrderID DESC) AS rn
    FROM    OrderTotals
)
SELECT  CustomerID, OrderID, OrderDate, CAST(OrderNet AS decimal(10,2)) AS OrderNet
FROM    Ranked
WHERE   rn = 1
ORDER BY OrderNet DESC, CustomerID;

Expected result: the same 35 rows as Day 3, from customer 26 (order 1014, 1577.70) to customer 7 (order 1142, 248.00). If two orders tied, the tie-breakers pick the more recent one — an explicit business rule rather than “return both”.

Worked example 5 — Customer quartiles with NTILE

WITH CustomerSales AS
(
    SELECT  o.CustomerID,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS LifetimeNet
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
    GROUP BY o.CustomerID
),
Bucketed AS
(
    SELECT  CustomerID, LifetimeNet,
            NTILE(4) OVER (ORDER BY LifetimeNet DESC, CustomerID) AS Quartile
    FROM    CustomerSales
)
SELECT  Quartile,
        COUNT(*)                              AS Customers,
        CAST(MIN(LifetimeNet) AS decimal(12,2)) AS LowestNet,
        CAST(MAX(LifetimeNet) AS decimal(12,2)) AS HighestNet
FROM    Bucketed
GROUP BY Quartile
ORDER BY Quartile;

Expected result

35 rows ÷ 4 = 8 remainder 3, so the first three quartiles get 9 customers each.

Worked example 6 — Share of total with windowed aggregates

WITH ProductSales AS
(
    SELECT  p.CategoryID,
            p.ProductCode,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS NetSales
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
    JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY p.CategoryID, p.ProductCode
)
SELECT  CategoryID,
        ProductCode,
        CAST(NetSales AS decimal(12,2))                                                   AS NetSales,
        CAST(100.0 * NetSales / SUM(NetSales) OVER (PARTITION BY CategoryID) AS decimal(5,1)) AS PctOfCategory,
        CAST(100.0 * NetSales / SUM(NetSales) OVER ()                        AS decimal(5,1)) AS PctOfTotal
FROM    ProductSales
WHERE   CategoryID = 2                    -- tents only, to keep the output short
ORDER BY NetSales DESC, ProductCode;

Expected result: 3 rows — TNT-002 (8777.35), TNT-003 (5241.45), TNT-001 (3562.65), with PctOfCategory 49.9, 29.8 and 20.3.

Trap: look at PctOfTotal. It shows 49.9, 29.8 and 20.3 — the same as PctOfCategory, adding up to 100. The WHERE CategoryID = 2 filter ran before the window function, so SUM(…) OVER () only saw tents. To show the share of all 2025 sales, filter in an outer query instead:

-- …same CTE…
SELECT * FROM ( SELECT CategoryID, ProductCode, NetSales,
                       100.0 * NetSales / SUM(NetSales) OVER () AS PctOfTotal
                FROM ProductSales ) AS x
WHERE  x.CategoryID = 2;   -- now PctOfTotal = 13.8, 8.2, 5.6

Worked example 7 — Flag duplicate staging rows

WITH Numbered AS
(
    SELECT  ImportRowID,
            FullName,
            Email,
            ROW_NUMBER() OVER (PARTITION BY TRIM(Email) ORDER BY ImportRowID) AS CopyNo,
            COUNT(*)     OVER (PARTITION BY TRIM(Email))                       AS CopiesOfEmail
    FROM    Staging.CustomerImport
)
SELECT  ImportRowID, FullName, Email, CopyNo, CopiesOfEmail,
        CASE WHEN Email IS NULL THEN 'Review: no email'
             WHEN CopyNo > 1   THEN 'Duplicate - discard'
             ELSE                   'Keep'
        END AS Action
FROM    Numbered
ORDER BY ImportRowID;

Expected result: 15 rows. Rows 4 and 5 (copies of Daisy Holt, row 3) and row 13 (copy of Kofi Mensah, row 6) are Duplicate – discard. Row 12 has no email and is flagged for review. The other 11 rows are Keep.

E. Guided practical lab — 2025 product league table

Merchandising wants a league table of 2025 products: overall rank, rank within top-level category, quartile, and share of the category’s sales. Then they want the top 3 per category.

Step 1 — Prepare one row per product with its top-level category

1. Do: Reuse Day 4’s tree to map categories to top-level names, and compute 2025 sales per product.
2. Run:

   USE RetailLab;
   GO
   
   WITH CategoryTree AS
   (
       SELECT CategoryID, CategoryName AS TopCategory
       FROM   Inventory.Category
       WHERE  ParentCategoryID IS NULL
       UNION ALL
       SELECT c.CategoryID, t.TopCategory
       FROM   Inventory.Category AS c
       JOIN   CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
   ),
   ProductSales AS
   (
       SELECT  t.TopCategory,
               p.ProductCode,
               p.ProductName,
               SUM(l.Quantity)                                      AS Units,
               SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS NetSales
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID    = o.OrderID
       JOIN    Inventory.Product    AS p ON p.ProductID  = l.ProductID
       JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
       GROUP BY t.TopCategory, p.ProductCode, p.ProductName
   )
   SELECT  COUNT(*) AS Products, CAST(SUM(NetSales) AS decimal(12,2)) AS NetSales
   FROM    ProductSales;

3. Observe: 28, 63741.94.
4. Why it matters: Control total confirmed before ranking anything.
5. Confirm: 28 products; 63741.94.

Step 2 — Add the rankings

1. Do: Replace the final SELECT with one that adds window functions.

2. Run:

   SELECT  TopCategory,
           ProductCode,
           ProductName,
           Units,
           CAST(NetSales AS decimal(12,2))                                                AS NetSales,
           RANK()       OVER (ORDER BY NetSales DESC)                                     AS OverallRank,
           ROW_NUMBER() OVER (PARTITION BY TopCategory ORDER BY NetSales DESC, ProductCode) AS RankInCategory,
           NTILE(4)     OVER (ORDER BY NetSales DESC, ProductCode)                        AS Quartile,
           CAST(100.0 * NetSales / SUM(NetSales) OVER (PARTITION BY TopCategory)
                AS decimal(5,1))                                                          AS PctOfCategory
   FROM    ProductSales
   ORDER BY OverallRank, ProductCode;

3. Observe: 28 rows. The first five:

   TopCategory	ProductCode	Units	NetSales	OverallRank	RankInCategory	Quartile	PctOfCategory
   Camping	TNT-002	26	8777.35	1	1	1	31.8
   Camping	TNT-003	22	5241.45	2	2	1	19.0
   Camping	SLP-002	17	4903.60	3	3	1	17.8
   Clothing	JKT-001	27	4866.75	4	1	1	19.9
   Accessories	NAV-002	14	4021.55	5	1	1	34.3

   The last row is COK-003 (Gas Canister): rank 28, quartile 4, 0.8% of Camping — yet it sold the most units (38).

4. Why it matters: Four different windows in one query, each with its own partition and order, all computed over the same 28 rows.

5. Confirm: 28 rows; each quartile contains 7 products (28 ÷ 4).

Step 3 — Top 3 per category

1. Do: Window functions cannot go in WHERE. Turn the step-2 query into a third CTE called League (remove its ORDER BY) and filter it.

2. Run: after ProductSales, add:

   ,
   League AS
   (
       SELECT  TopCategory, ProductCode, ProductName, Units, NetSales,
               ROW_NUMBER() OVER (PARTITION BY TopCategory ORDER BY NetSales DESC, ProductCode) AS RankInCategory,
               CAST(100.0 * NetSales / SUM(NetSales) OVER (PARTITION BY TopCategory)
                    AS decimal(5,1))                                                            AS PctOfCategory
       FROM    ProductSales
   )
   SELECT  TopCategory, RankInCategory, ProductCode, ProductName,
           CAST(NetSales AS decimal(12,2)) AS NetSales, PctOfCategory
   FROM    League
   WHERE   RankInCategory <= 3
   ORDER BY TopCategory, RankInCategory;

3. Observe: 9 rows.

   TopCategory	RankInCategory	ProductCode	ProductName	NetSales	PctOfCategory
   Accessories	1	NAV-002	Handheld GPS Unit	4021.55	34.3
   Accessories	2	BPK-002	Expedition Pack 65L	3721.30	31.7
   Accessories	3	BPK-001	Daypack 25L	1652.55	14.1
   Camping	1	TNT-002	Summit 4-Person Tent	8777.35	31.8
   Camping	2	TNT-003	Ultralight Solo Shelter	5241.45	19.0
   Camping	3	SLP-002	Arctic Down Sleeping Bag	4903.60	17.8
   Clothing	1	JKT-001	Storm Shell Waterproof Jacket	4866.75	19.9
   Clothing	2	FTW-001	Ridge Walking Boot	3996.25	16.4
   Clothing	3	JKT-003	Glen Insulated Jacket	3354.90	13.7

4. Why it matters: PctOfCategory was computed before the filter, inside the CTE, so it still reflects the whole category. Had you computed it after filtering to the top 3, the percentages would only describe those 3 rows (the trap in worked example 6).

5. Confirm: 9 rows; the three Accessories percentages sum to 80.1.

F. Independent practice

Task 1 — Most frequent customers (RANK vs DENSE_RANK)

Rank customers by their number of non-cancelled orders. Return CustomerID, Orders, OrderRank (RANK) and DenseRank (DENSE_RANK), keeping customers whose dense rank is 5 or better. Sort by Orders descending, then CustomerID.

Success criteria: 9 rows. Customers 1, 12 and 18 share rank 1 (27 orders); customers 6 and 26 have OrderRank 4 but DenseRank 2; customer 29 (16 orders) is last with OrderRank 9 and DenseRank 5.

Task 2 — Each customer’s most recent order

Return each ordering customer’s most recent order (any status): CustomerID, OrderID, OrderDate, OrderStatus and SalesChannel. If a customer placed two orders on that date, take the higher OrderID. Sort by OrderDate descending, then CustomerID.

Success criteria: 35 rows. The first is customer 17 (order 1376, 2025-12-31, Delivered, Store); next are customers 15 and 26 (orders 1374 and 1375, 2025-12-30, Pending).

Task 3 — Top salesperson in each shop, 2025

For 2025 store orders (non-cancelled), find the salesperson with the highest net sales in each store. Return StoreID, EmployeeID, EmployeeName, Orders and NetSales. Use RANK so that a tie would return both people. Sort by StoreID.

Success criteria: 3 rows — store 1: Sophie Turner (7, 7 orders, 2430.97); store 2: Liam O’Connor (6, 8 orders, 2732.85); store 3: Chen Wei (10, 6 orders, 3468.18).

Task 4 — Clean the price updates (challenging)

Staging.ProductPriceUpdate contains messy product codes (' jkt-005 '), a NULL code and two updates for LGT-001. Return one row per cleaned product code (UPPER(TRIM(ProductCode))), keeping the update with the highest RowID, and ignore rows with no code. Return CleanCode, RowID, NewListPriceText and UpdatesForCode. Sort by CleanCode.

Success criteria: 8 rows — BPK-001, COK-003, JKT-005, LGT-001 (RowID 7, 42.00, 2 updates), NAV-003, SLP-003, TNT-001, TNT-099. Row 6 is discarded as superseded, row 10 as having no code. (Invalid prices and unknown codes are dealt with on Day 15.)

Stretch challenge — Top 5 by units, ties included

For 2025, list the products ranked in the top 5 distinct unit totals within each top-level category. Return TopCategory, UnitRank, ProductCode, ProductName and Units. Sort by TopCategory, UnitRank, ProductCode.

Success criteria: 16 rows: 5 Accessories, 5 Camping and 6 Clothing — BSL-001 and JKT-004 both sold 20 units and share rank 5.

G. Solutions and explanations

WITH CustomerOrders AS
(
    SELECT  CustomerID,
            COUNT(*)                              AS Orders,
            RANK()       OVER (ORDER BY COUNT(*) DESC) AS OrderRank,
            DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS DenseRank
    FROM    Sales.SalesOrder
    WHERE   OrderStatus <> 'Cancelled'
    GROUP BY CustomerID
)
SELECT  CustomerID, Orders, OrderRank, DenseRank
FROM    CustomerOrders
WHERE   DenseRank <= 5
ORDER BY Orders DESC, CustomerID;

WITH Ranked AS
(
    SELECT  CustomerID, OrderID, OrderDate, OrderStatus, SalesChannel,
            ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM    Sales.SalesOrder
)
SELECT  CustomerID, OrderID, OrderDate, OrderStatus, SalesChannel
FROM    Ranked
WHERE   rn = 1
ORDER BY OrderDate DESC, CustomerID;

WITH StaffSales AS
(
    SELECT  o.StoreID,
            o.EmployeeID,
            COUNT(DISTINCT o.OrderID)                            AS Orders,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))  AS NetSales
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.SalesChannel = 'Store'
      AND   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.StoreID, o.EmployeeID
),
Ranked AS
(
    SELECT  *, RANK() OVER (PARTITION BY StoreID ORDER BY NetSales DESC) AS rk
    FROM    StaffSales
)
SELECT  r.StoreID,
        r.EmployeeID,
        CONCAT_WS(N' ', e.FirstName, e.LastName) AS EmployeeName,
        r.Orders,
        CAST(r.NetSales AS decimal(12,2))        AS NetSales
FROM    Ranked      AS r
JOIN    HR.Employee AS e ON e.EmployeeID = r.EmployeeID
WHERE   r.rk = 1
ORDER BY r.StoreID, r.EmployeeID;

WITH Cleaned AS
(
    SELECT  UPPER(TRIM(ProductCode)) AS CleanCode,
            RowID,
            NewListPriceText,
            ROW_NUMBER() OVER (PARTITION BY UPPER(TRIM(ProductCode)) ORDER BY RowID DESC) AS rn,
            COUNT(*)     OVER (PARTITION BY UPPER(TRIM(ProductCode)))                     AS UpdatesForCode
    FROM    Staging.ProductPriceUpdate
    WHERE   ProductCode IS NOT NULL
)
SELECT  CleanCode, RowID, NewListPriceText, UpdatesForCode
FROM    Cleaned
WHERE   rn = 1
ORDER BY CleanCode;

WITH CategoryTree AS
(
    SELECT CategoryID, CategoryName AS TopCategory
    FROM   Inventory.Category WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, t.TopCategory
    FROM   Inventory.Category AS c
    JOIN   CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
),
Units2025 AS
(
    SELECT  t.TopCategory, p.ProductCode, p.ProductName, SUM(l.Quantity) AS Units
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID    = o.OrderID
    JOIN    Inventory.Product    AS p ON p.ProductID  = l.ProductID
    JOIN    CategoryTree         AS t ON t.CategoryID = p.CategoryID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY t.TopCategory, p.ProductCode, p.ProductName
),
Ranked AS
(
    SELECT  *, DENSE_RANK() OVER (PARTITION BY TopCategory ORDER BY Units DESC) AS UnitRank
    FROM    Units2025
)
SELECT  TopCategory, UnitRank, ProductCode, ProductName, Units
FROM    Ranked
WHERE   UnitRank <= 5
ORDER BY TopCategory, UnitRank, ProductCode;

H. Knowledge check

1. Understanding. Salaries 90, 80, 80, 70 are ranked in descending order. What do ROW_NUMBER, RANK and DENSE_RANK return for the value 70?

2. Debugging. Why does this fail, and how do you fix it?

   SELECT EmployeeID, Salary, RANK() OVER (ORDER BY Salary DESC) AS rk
   FROM   HR.Employee
   WHERE  RANK() OVER (ORDER BY Salary DESC) <= 3;

3. Query interpretation. A query uses ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) to pick each customer’s latest order. Run twice, it returns different OrderIDs for customer 6. Why?

4. Understanding. NTILE(4) over 10 rows: how many rows are in each bucket?

5. Debugging. A report shows each product’s percentage of total sales, but after adding WHERE CategoryID = 2 the percentages for tents add up to 100. The analyst expected about 27.6%. Explain.

I. Troubleshooting and cleanup

Cleanup. Day 5 is read-only; nothing to clean up. Keep RetailLab unchanged for Day 6.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 shows Tom and Aisha with RANK 4 and different quartiles, and you can explain why.
• Worked example 6’s trap made sense: you saw PctOfTotal change once the filter moved outside the window.
• The guided lab returns the 9-row top-3 table with Accessories percentages summing to 80.1.
• Tasks 1–4 match the success criteria (9, 35, 3 and 8 rows).
• You can state when to use each of the four ranking functions.

Recap

• A window function returns a value for every row; PARTITION BY restarts it per group; ORDER BY inside OVER orders the calculation, not the output.
• Window functions run in the SELECT phase — after WHERE, GROUP BY and HAVING — so filter on them in an outer query.
• ROW_NUMBER is unique (add a tie-breaker); RANK shares ranks and leaves gaps; DENSE_RANK shares ranks without gaps; NTILE splits rows into equal-sized buckets.
• Top-N per group and de-duplication are both “rank in a CTE, filter on the rank”.
• Aggregates with OVER give group totals and shares next to detail rows; window functions can also rank aggregated (GROUP BY) results.

Next: Day 6 — Window functions part 2: LAG, LEAD, FIRST_VALUE, LAST_VALUE, running totals, moving averages and explicit window frames.

References (Microsoft Learn)

• SELECT – OVER clause (Transact-SQL)
• Ranking functions (Transact-SQL)
• ROW_NUMBER (Transact-SQL)
• RANK (Transact-SQL)
• DENSE_RANK (Transact-SQL)
• NTILE (Transact-SQL)
• SELECT – WINDOW clause (Transact-SQL)
• TRIM (Transact-SQL)