Day 7: Week 1 review & integration

A. Lesson overview

Activity breakdown

Business scenario. The commercial director wants a customer scorecard for the 2026 planning meeting: one row per customer with their order history, value, recency, rank, value quartile and a simple segment label (Champion, At risk, New, Regular). It must reconcile to the finance figures. It is a realistic piece of analyst work that uses almost everything from Week 1.

B. Theory explained clearly

Part 1 — Logical query processing: the full picture

SQL Server is free to execute a query in any physical order it likes, but the result must be as if the clauses were processed in this logical order:

This one table explains most of the “why does this fail?” moments of Week 1:

Within a single SELECT list, one expression cannot refer to another’s alias: SELECT a + b AS c, c * 2 AS d fails. Use a CTE, a derived table or CROSS APPLY (Day 10).

When DISTINCT is used, ORDER BY may only refer to columns that appear in the SELECT list, because sorting happens after duplicates are removed.

Part 2 — TOP and OFFSET … FETCH

B1. TOP

SELECT TOP (5) ProductCode, ListPrice
FROM   Inventory.Product
ORDER BY ListPrice DESC, ProductCode;

Rules:

• Always use ORDER BY with TOP. Without it, SQL Server returns some n rows — whichever are cheapest to produce. They may differ between runs.
• WITH TIES requires ORDER BY, and ties are judged only on the ORDER BY columns. If you include a unique tie-breaker in ORDER BY, there can be no ties, so WITH TIES has no effect.
• Use parentheses: TOP (5). They are required when n is a variable or expression (TOP (@n)), and recommended always.
• TOP is T-SQL syntax. The ANSI equivalent is OFFSET 0 ROWS FETCH NEXT n ROWS ONLY.

TOP versus ROW_NUMBER: TOP returns the first n rows of the whole result. For “top n per group” you need the Day 5 pattern (ROW_NUMBER in a CTE), or CROSS APPLY with TOP (Day 10).

B2. OFFSET … FETCH

SELECT  CustomerID, LastName, FirstName
FROM    Sales.Customer
ORDER BY LastName, CustomerID
OFFSET 20 ROWS                -- skip the first 20
FETCH NEXT 10 ROWS ONLY;      -- return the next 10  (page 3 of 10 rows)

• OFFSET … FETCH is part of ORDER BY; it cannot be used without it.
• OFFSET 0 ROWS is valid; FETCH is optional (omit it to return everything after the offset).
• Values can be variables: OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY.
• The order must be unique, or a row can appear on two pages, or on none, as tied rows shuffle between executions. LastName alone is not unique (customers 3 and others may share surnames in future data); LastName, CustomerID is.
• Each page re-runs the query and skips rows; page 1,000 has to read and discard 9,990 rows first. For deep paging over large tables, keyset paging (WHERE (LastName > @LastSeenName) OR (LastName = @LastSeenName AND CustomerID > @LastSeenID)) scales better. You will revisit this on Day 24.
• Rows inserted or deleted between page requests shift later pages. Paging is a presentation technique, not a consistent snapshot.

Part 3 — Choosing the right tool

And three habits that apply to every query:

1. Know the grain of every intermediate result (one row per what?).
2. Validate against a control total before building on a step.
3. Make ordering deterministic wherever order matters — in ORDER BY, OVER (ORDER BY …), TOP and paging.

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: 35, 369, 122354.88.

USE RetailLab;
GO

SELECT  COUNT(DISTINCT o.CustomerID)                                              AS OrderingCustomers,
        COUNT(DISTINCT o.OrderID)                                                 AS NonCancelledOrders,
        CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2)) AS NetSales
FROM    Sales.SalesOrder     AS o
JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled';

D. Worked examples

Worked example 1 — TOP with and without ties

WITH CustomerOrders AS
(
    SELECT CustomerID, COUNT(*) AS Orders
    FROM   Sales.SalesOrder
    WHERE  OrderStatus <> 'Cancelled'
    GROUP BY CustomerID
)
SELECT TOP (4) CustomerID, Orders
FROM   CustomerOrders
ORDER BY Orders DESC;                    -- non-deterministic at the 4th row!

WITH CustomerOrders AS
(
    SELECT CustomerID, COUNT(*) AS Orders
    FROM   Sales.SalesOrder
    WHERE  OrderStatus <> 'Cancelled'
    GROUP BY CustomerID
)
SELECT TOP (4) WITH TIES CustomerID, Orders
FROM   CustomerOrders
ORDER BY Orders DESC;

Expected result: the first query returns 4 rows: customers 1, 12 and 18 (27 orders each) and one of customers 6 or 26 (21 orders) — which one is not guaranteed. The second returns 5 rows, including both 6 and 26, because they tie on the ORDER BY value.

What to notice: adding CustomerID to the ORDER BY would make the first query deterministic (customer 6 would be chosen) but would make WITH TIES pointless. Decide which behaviour the business wants: “exactly four” or “everyone tied for the top four positions”.

Worked example 2 — Page 3 of the customer list

DECLARE @PageNumber int = 3,
        @PageSize   int = 10;

SELECT  CustomerID, LastName, FirstName
FROM    Sales.Customer
ORDER BY LastName, CustomerID
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Expected result: 10 rows — Kelly (14), Lefèvre (33), Lindqvist (38), Lopez (23), MacDonald (8), Martin (11), Mehta (39), Morgan (1), Murphy (9), Okoye (28). Page 4 (@PageNumber = 4) returns the last 10 customers.

Worked example 3 — Alias visibility within a SELECT

-- Fails: Invalid column name 'UnitMargin'.
SELECT  ProductCode,
        ListPrice - StandardCost              AS UnitMargin,
        UnitMargin / NULLIF(ListPrice, 0)     AS MarginRatio
FROM    Inventory.Product;

-- Works: define the alias in one layer, use it in the next.
WITH m AS
(
    SELECT ProductCode, ListPrice, ListPrice - StandardCost AS UnitMargin
    FROM   Inventory.Product
)
SELECT  ProductCode, UnitMargin,
        CAST(UnitMargin / NULLIF(ListPrice, 0) AS decimal(5,3)) AS MarginRatio
FROM    m
ORDER BY MarginRatio DESC, ProductCode;

Expected result: the first statement fails; the second returns 30 rows. All aliases in one SELECT list are created “at the same time”, so none can see another.

Worked example 4 — One question, three correct solutions

“Which customers placed an order in December 2025 worth more than £600?”

-- A: EXISTS with a correlated aggregate
SELECT c.CustomerID
FROM   Sales.Customer AS c
WHERE  EXISTS (SELECT 1
               FROM   Sales.SalesOrder     AS o
               JOIN   Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
               WHERE  o.CustomerID = c.CustomerID
                 AND  o.OrderStatus <> 'Cancelled'
                 AND  o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
               GROUP BY o.OrderID
               HAVING SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) > 600)
ORDER BY c.CustomerID;

-- B: CTE + DISTINCT
WITH DecOrders AS
(
    SELECT  o.CustomerID, o.OrderID,
            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'
      AND   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CustomerID, o.OrderID
)
SELECT DISTINCT CustomerID FROM DecOrders WHERE OrderNet > 600 ORDER BY CustomerID;

-- C: window function — largest December order per customer, then filter
WITH DecLines AS
(
    SELECT  o.CustomerID, o.OrderID,
            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'
      AND   o.OrderDate >= '2025-12-01' AND o.OrderDate < '2026-01-01'
    GROUP BY o.CustomerID, o.OrderID
),
Biggest AS
(
    SELECT CustomerID, MAX(OrderNet) OVER (PARTITION BY CustomerID) AS MaxDecOrder
    FROM   DecLines
)
SELECT DISTINCT CustomerID FROM Biggest WHERE MaxDecOrder > 600 ORDER BY CustomerID;

Expected result (all three): 6 customers — 1, 13, 17, 20, 23 and 28.

What to notice: all three are correct; A states the question most directly; B is the easiest to debug (run the CTE alone); C is overkill here but shows that window functions and grouping can be layered. Choose the version a colleague will understand.

If your result differs, run DecOrders on its own and inspect OrderNet: the six customers are those with at least one December order worth more than 600.00.

E. Guided practical lab — Customer scorecard

Build one row per ordering customer with: orders, lifetime net, average order value, first and last order dates, days since the last order (as at 31 December 2025), 2024 and 2025 net sales, value rank, value quartile and a segment.

Segment rules (agreed with the commercial director)

Step 1 — One row per order (grain: order)

1. Do: Create the base CTE.
2. Run:

   USE RetailLab;
   GO
   
   DECLARE @AsAt date = '2025-12-31';
   
   WITH OrderTotals AS
   (
       SELECT  o.CustomerID, o.OrderID, 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.CustomerID, o.OrderID, o.OrderDate
   )
   SELECT COUNT(*) AS Orders, CAST(SUM(OrderNet) AS decimal(12,2)) AS NetSales
   FROM   OrderTotals;

3. Observe: 369, 122354.88.
4. Why it matters: Control total: the scorecard must add up to this.
5. Confirm: matches the validation query.

Step 2 — One row per customer (grain: customer), with conditional aggregation

1. Do: Add a CustomerStats CTE after OrderTotals.
2. Run: replace the final SELECT:

   , CustomerStats AS
   (
       SELECT  CustomerID,
               COUNT(*)                                                     AS Orders,
               SUM(OrderNet)                                                AS LifetimeNet,
               MIN(OrderDate)                                               AS FirstOrder,
               MAX(OrderDate)                                               AS LastOrder,
               SUM(CASE WHEN OrderDate <  '2025-01-01' THEN OrderNet ELSE 0 END) AS Net2024,
               SUM(CASE WHEN OrderDate >= '2025-01-01' THEN OrderNet ELSE 0 END) AS Net2025
       FROM    OrderTotals
       GROUP BY CustomerID
   )
   SELECT COUNT(*) AS Customers, CAST(SUM(LifetimeNet) AS decimal(12,2)) AS NetSales,
          CAST(SUM(Net2024) AS decimal(12,2)) AS Net2024, CAST(SUM(Net2025) AS decimal(12,2)) AS Net2025
   FROM   CustomerStats;

3. Observe: 35, 122354.88, 58612.94, 63741.94.
4. Why it matters: Two grains, two control checks — the year split also matches Day 2.
5. Confirm: the three totals match.

Step 3 — Add recency, rank and quartile (window functions over customers)

1. Do: Add a Scored CTE.
2. Run: replace the final SELECT:

   , Scored AS
   (
       SELECT  *,
               DATEDIFF(DAY, LastOrder, @AsAt)                                AS DaysSinceLast,
               RANK()   OVER (ORDER BY LifetimeNet DESC)                      AS ValueRank,
               NTILE(4) OVER (ORDER BY LifetimeNet DESC, CustomerID)          AS ValueQuartile
       FROM    CustomerStats
   )
   SELECT CustomerID, Orders, CAST(LifetimeNet AS decimal(12,2)) AS LifetimeNet,
          DaysSinceLast, ValueRank, ValueQuartile
   FROM   Scored
   ORDER BY ValueRank, CustomerID;

3. Observe: 35 rows. Customer 1 is rank 1 (9301.71, 2 days since last order); customer 35 is rank 35.
4. Why it matters: Window functions run on the 35 customer rows, after GROUP BY in the previous CTE.
5. Confirm: quartile sizes 9, 9, 9, 8.

Step 4 — Apply the segment rules and finish the scorecard

1. Do: Replace the final SELECT with the full output.

2. Run:

   SELECT  s.CustomerID,
           CONCAT_WS(N' ', c.FirstName, c.LastName)                 AS CustomerName,
           s.Orders,
           CAST(s.LifetimeNet AS decimal(12,2))                     AS LifetimeNet,
           CAST(s.LifetimeNet / s.Orders AS decimal(10,2))          AS AvgOrderValue,
           s.FirstOrder,
           s.LastOrder,
           s.DaysSinceLast,
           CAST(s.Net2024 AS decimal(12,2))                         AS Net2024,
           CAST(s.Net2025 AS decimal(12,2))                         AS Net2025,
           s.ValueRank,
           s.ValueQuartile,
           CASE
               WHEN s.ValueQuartile = 1  AND s.DaysSinceLast <= 60 THEN 'Champion'
               WHEN s.ValueQuartile <= 2 AND s.DaysSinceLast >  90 THEN 'At risk'
               WHEN s.FirstOrder >= '2025-07-01'                    THEN 'New'
               ELSE                                                      'Regular'
           END                                                      AS Segment
   FROM    Scored          AS s
   JOIN    Sales.Customer  AS c ON c.CustomerID = s.CustomerID
   ORDER BY s.ValueRank, s.CustomerID;

   (Highlight from DECLARE to the end, so @AsAt is defined.)

3. Observe: 35 rows. The first ten:

   CustomerID	CustomerName	Orders	LifetimeNet	AvgOrderValue	DaysSinceLast	Net2024	Net2025	ValueRank	ValueQuartile	Segment
   1	Alice Morgan	27	9301.71	344.51	2	4387.48	4914.22	1	1	Champion
   18	Sofia Rossi	27	8883.52	329.02	26	6053.05	2830.47	2	1	Champion
   12	Zoë Bennett	27	8810.01	326.30	8	5042.62	3767.39	3	1	Champion
   6	Farah Ali	21	7178.50	341.83	24	3011.75	4166.75	4	1	Champion
   26	Amelia Brooks	21	6561.86	312.47	1	3897.90	2663.96	5	1	Champion
   29	Henry Shaw	16	6323.12	395.20	69	1843.87	4479.25	6	1	Regular
   13	Mohammed Rahman	19	6062.85	319.10	4	1729.00	4333.85	7	1	Champion
   4	David Thompson	18	5840.29	324.46	23	3497.60	2342.69	8	1	Champion
   23	Xavier Lopez	18	5670.92	315.05	18	2767.74	2903.18	9	1	Champion
   9	Jack Murphy	14	4905.59	350.40	20	4239.64	665.95	10	2	Regular

4. Why it matters: Every column traces back to a validated step. The JOIN to Sales.Customer happens last, at the customer grain, so it cannot multiply rows.

5. Confirm: segment counts — 8 Champion, 2 At risk (customers 27 and 25), 1 New (customer 35), 24 Regular.

Step 5 — Challenge the rules

1. Do: Look at customer 14 (Niamh Kelly, 554 days since last order, quartile 3) and customer 9 (Jack Murphy, 2025 sales down 84%). Both are Regular.
2. Run: no new SQL — or filter the step-4 output with an outer query on Segment = 'Regular' AND DaysSinceLast > 180.
3. Observe: the agreed rules miss lost lower-value customers and sharply declining high-value ones.
4. Why it matters: SQL implements the rules precisely; whether the rules are good is a business question. Task 4 below quantifies the decliners.
5. Confirm: you can propose one extra rule and say where in the CASE it should go (order matters: first match wins).

F. Independent practice

Task 1 — Top products with ties

Return the 2025 products with the top 4 unit totals, including any products tied with the fourth (non-cancelled orders). Return ProductCode, ProductName and Units, sorted by units descending, then product code.

Success criteria: 4 rows — COK-003 (38), FTW-001 (30), JKT-001 (27) and TNT-002 (26). No other product sold 26 units, so WITH TIES adds nothing here. Then change the query to TOP (7) WITH TIES: it returns 9 rows, because three products tie on the seventh value, 22 units (BPK-003, JKT-003, TNT-003).

Task 2 — Paging a product catalogue

Using variables @PageNumber and @PageSize, return a page of products sorted by ListPrice descending then ProductCode. Return ProductCode, ProductName, ListPrice and RowNo (the position in the full sorted list).

Success criteria: with page size 8, page 2 returns positions 9–16: JKT-002 (189.00), TNT-001 (189.00), JKT-003, JKT-004 (159.00), FTW-001, FTW-002 (139.00), SLP-001 (129.00), FTW-003 (109.00). Page 4 returns 6 rows.

Task 3 — Each category’s two best months in 2025

For each top-level category, return its two best months of 2025 by net sales: TopCategory, MonthNo, NetSales, MonthRank and PctOfCategoryYear (1 decimal place). Sort by category and rank.

Success criteria: 6 rows. Accessories: December (2907.17, 24.8%) and June (1704.60, 14.5%). Camping: April (3826.05) and December (3626.90). Clothing: May (3349.25) and December (3184.58).

Task 4 — Sharply declining customers (challenging)

Find customers who bought in 2024 and whose 2025 net sales are less than half of their 2024 net sales (including customers with no 2025 sales). Return CustomerID, Net2024, Net2025, ChangePct (1 decimal place) and DeclineRank (1 = largest drop in pounds). Sort by DeclineRank.

Success criteria: 9 rows. Rank 1: customer 9 (4239.64 → 665.95, −84.3%). Rank 2: customer 18 (6053.05 → 2830.47, −53.2%). Customer 14 appears with 0.00 and −100.0%.

Stretch challenge — Cohort retention

Group ordering customers into cohorts by the quarter of their first order (e.g. 2024-Q1). For each cohort return Cohort, Customers, ActiveH2_2025 (customers with at least one order from 1 July 2025) and RetentionPct. Sort by cohort.

Success criteria: 6 cohorts. 2024-Q1: 19 customers, 16 active, 84.2%. Every later cohort shows 100.0%. Cohorts with no members (2025-Q2, 2025-Q3) do not appear.

G. Solutions and explanations

WITH Units2025 AS
(
    SELECT  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
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY p.ProductCode, p.ProductName
),
TopUnits AS
(
    SELECT TOP (4) WITH TIES ProductCode, ProductName, Units
    FROM   Units2025
    ORDER BY Units DESC                     -- no tie-breaker here, on purpose
)
SELECT ProductCode, ProductName, Units
FROM   TopUnits
ORDER BY Units DESC, ProductCode;           -- deterministic presentation order

DECLARE @PageNumber int = 2,
        @PageSize   int = 8;

SELECT  ProductCode,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER (ORDER BY ListPrice DESC, ProductCode) AS RowNo
FROM    Inventory.Product
ORDER BY ListPrice DESC, ProductCode
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

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
),
MonthlyCat AS
(
    SELECT  t.TopCategory,
            MONTH(o.OrderDate)                                   AS MonthNo,
            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, MONTH(o.OrderDate)
),
Ranked AS
(
    SELECT  *,
            RANK() OVER (PARTITION BY TopCategory ORDER BY NetSales DESC) AS MonthRank,
            100.0 * NetSales / SUM(NetSales) OVER (PARTITION BY TopCategory) AS PctOfCategoryYear
    FROM    MonthlyCat
)
SELECT  TopCategory, MonthNo,
        CAST(NetSales AS decimal(12,2))          AS NetSales,
        MonthRank,
        CAST(PctOfCategoryYear AS decimal(5,1))  AS PctOfCategoryYear
FROM    Ranked
WHERE   MonthRank <= 2
ORDER BY TopCategory, MonthRank;

WITH OrderTotals AS
(
    SELECT  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.CustomerID, o.OrderID, o.OrderDate
),
YearSplit AS
(
    SELECT  CustomerID,
            SUM(CASE WHEN OrderDate <  '2025-01-01' THEN OrderNet ELSE 0 END) AS Net2024,
            SUM(CASE WHEN OrderDate >= '2025-01-01' THEN OrderNet ELSE 0 END) AS Net2025
    FROM    OrderTotals
    GROUP BY CustomerID
)
SELECT  CustomerID,
        CAST(Net2024 AS decimal(12,2))                                   AS Net2024,
        CAST(Net2025 AS decimal(12,2))                                   AS Net2025,
        CAST(100.0 * (Net2025 - Net2024) / Net2024 AS decimal(6,1))      AS ChangePct,
        RANK() OVER (ORDER BY Net2024 - Net2025 DESC)                    AS DeclineRank
FROM    YearSplit
WHERE   Net2024 > 0
  AND   Net2025 < 0.5 * Net2024
ORDER BY DeclineRank, CustomerID;

WITH CustomerActivity AS
(
    SELECT  CustomerID,
            MIN(OrderDate)                                            AS FirstOrder,
            MAX(CASE WHEN OrderDate >= '2025-07-01' THEN 1 ELSE 0 END) AS ActiveH2_2025
    FROM    Sales.SalesOrder
    WHERE   OrderStatus <> 'Cancelled'
    GROUP BY CustomerID
)
SELECT  CONCAT(YEAR(FirstOrder), '-Q', DATEPART(QUARTER, FirstOrder))      AS Cohort,
        COUNT(*)                                                           AS Customers,
        SUM(ActiveH2_2025)                                                 AS ActiveH2_2025,
        CAST(100.0 * SUM(ActiveH2_2025) / COUNT(*) AS decimal(5,1))        AS RetentionPct
FROM    CustomerActivity
GROUP BY CONCAT(YEAR(FirstOrder), '-Q', DATEPART(QUARTER, FirstOrder))
ORDER BY Cohort;

H. Knowledge check (Week 1)

1. Understanding. Put these clauses in logical processing order: ORDER BY, HAVING, SELECT, FROM, WHERE, GROUP BY, OFFSET … FETCH.

2. Query interpretation. What does SELECT TOP (3) WITH TIES ProductCode FROM Inventory.Product ORDER BY ListPrice DESC; return, and how many rows?

3. Debugging. A paging query uses ORDER BY LastName OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY. Users report a customer appearing on pages 1 and 2. Why?

4. Debugging. Why does SELECT CustomerID, COUNT(*) AS Orders FROM Sales.SalesOrder WHERE Orders > 5 GROUP BY CustomerID; fail, and what are two correct alternatives?

5. Mixed. For each of these, name the Week 1 technique you would reach for first: (a) each employee’s whole management chain; (b) month-on-month change; (c) the three most expensive products per category; (d) customers who never bought a jacket; (e) sales by year with a grand total row.

I. Troubleshooting and cleanup

Cleanup. Day 7 is read-only; nothing to clean up. RetailLab stays in its core state for Week 2.

J. Completion checklist, recap and references

Completion checklist

• You can reproduce the 8-step logical processing table from memory.
• Worked example 1 showed 4 rows without and 5 rows with WITH TIES.
• Your scorecard has 35 rows, reconciles to 122354.88 and shows 8 / 2 / 1 / 24 segment counts.
• Tasks 1–4 match the success criteria (4, 8, 6 and 9 rows).
• You answered the Week 1 knowledge check without looking back.

Week 1 recap

Next: Day 8 — Advanced JOIN techniques: every join type, row multiplication, unmatched rows, NULLs in join conditions and where to put filters.

References (Microsoft Learn)

• SELECT (Transact-SQL) — logical processing order
• TOP (Transact-SQL)
• ORDER BY clause (Transact-SQL) — OFFSET and FETCH
• WITH common_table_expression (Transact-SQL)
• SELECT – OVER clause (Transact-SQL)