Day 4: Common table expressions

A. Lesson overview

Activity breakdown

Business scenario. Ridgeway Outdoor’s management team wants three things before the planning meeting: an organisation chart with every reporting line, a sales report by top-level category even though products are attached to the lowest level of the category tree, and a daily December trading calendar that shows days with no sales. Each needs either a multi-step calculation or a walk through a hierarchy — the two jobs CTEs do best.

B. Theory explained clearly

Part 1 — Primer: variables and batches

B1. Variables

A variable holds a single value for the duration of a batch. Its name starts with @.

USE RetailLab;
GO

DECLARE @StartDate date = '2025-12-01';         -- declare and initialise
DECLARE @EndDate   date;                        -- declare only: value is NULL
SET     @EndDate   = '2025-12-31';              -- assign

DECLARE @Orders int;
SELECT  @Orders = COUNT(*)                      -- assign from a query
FROM    Sales.SalesOrder
WHERE   OrderDate >= @StartDate AND OrderDate <= @EndDate;

SELECT  @StartDate AS StartDate, @EndDate AS EndDate, @Orders AS DecemberOrders;   -- 27

• A declared but unassigned variable is NULL.
• SET assigns one variable; SELECT @a = …, @b = … can assign several at once from a query.
• If a SELECT … @x = col query returns several rows, @x ends up with a value from one of them (typically the last processed), without error. Use an aggregate or make sure the query returns one row.
• Variables make lesson queries easy to re-run with different parameters. On Day 17 you will see that stored procedure parameters behave like variables.

B2. Batches and GO

A batch is a group of statements sent to SQL Server together. In SSMS, GO on its own line separates batches. It is a client instruction, not T-SQL.

Variables live only within their batch:

DECLARE @Threshold int = 10;
GO
SELECT @Threshold;      -- fails: Must declare the scalar variable "@Threshold".

Highlighting part of a script and pressing F5 also defines a batch: if you highlight a query that uses @StartDate without its DECLARE, you get the same error. Highlight the declarations too.

Part 2 — Simple and multiple CTEs

B3. What a CTE is

A common table expression is a named, temporary result set defined with WITH and used by the single statement that immediately follows.

WITH OrderTotals AS                      -- 1. name
(
    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          -- 2. definition
)
SELECT  CustomerID, COUNT(*) AS Orders, CAST(AVG(OrderNet) AS decimal(10,2)) AS AvgOrder
FROM    OrderTotals                                          -- 3. use
GROUP BY CustomerID
ORDER BY CustomerID;

A CTE is like a derived table that has been moved to the top and given a name. Benefits:

What a CTE is not:

• It is not stored. It exists only for the one statement. A second SELECT after the first cannot use it.
• It is not a temporary table. SQL Server does not normally calculate it once and keep the result; each reference is expanded into the query, much as if you had written the derived table in that place. Referencing an expensive CTE three times may do the expensive work three times. On Day 15 you will meet temporary tables and table variables, which do store results.

B4. Syntax rules

• Optional column list: WITH OrderTotals (OrderID, CustomerID, OrderDate, OrderNet) AS ( … ). If present, it names the columns; otherwise every column in the inner SELECT needs a name.

• Multiple CTEs: separate them with commas and use WITH only once. A later CTE may refer to an earlier one.

  WITH A AS ( … ),
       B AS ( SELECT … FROM A … ),
       C AS ( SELECT … FROM B JOIN A … )
  SELECT … FROM C;

• Semicolon before WITH. WITH has other meanings in T-SQL, so the statement before a CTE must end with a semicolon. Otherwise you get Incorrect syntax near the keyword ‘with’. If this statement is a common table expression… the previous statement must be terminated with a semicolon. Some developers write ;WITH defensively; ending every statement with a semicolon is the cleaner habit.

• No ORDER BY inside a CTE (unless with TOP or OFFSET), for the same reason as derived tables. Sort in the outer statement.

• The statement after the CTE can be SELECT, INSERT, UPDATE, DELETE or MERGE (Day 15 uses this).

B5. Referencing a CTE twice: a self-join

Because a CTE has a name, you can join it to itself — for example, to put each month next to the previous month:

WITH Monthly AS
(
    SELECT  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
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY MONTH(o.OrderDate)
)
SELECT  cur.MonthNo,
        CAST(cur.NetSales  AS decimal(12,2)) AS NetSales,
        CAST(prev.NetSales AS decimal(12,2)) AS PrevMonthNetSales
FROM    Monthly AS cur
LEFT JOIN Monthly AS prev ON prev.MonthNo = cur.MonthNo - 1     -- January has no previous month
ORDER BY cur.MonthNo;

On Day 6 LAG() will do this more simply, but the self-join technique works for any relationship you can express in an ON clause.

Part 3 — Recursive CTEs

B6. Hierarchies stored as parent references

HR.Employee.ManagerID points to another employee; Inventory.Category.ParentCategoryID points to another category. This adjacency list design is simple to maintain, but answering “who is anywhere below Raj Patel?” needs one join per level — and you don’t know in advance how many levels there are. A recursive CTE repeats the join for you until there are no more levels.

B7. Anatomy of a recursive CTE

WITH Org AS
(
    -- 1. Anchor member: the starting rows (level 0)
    SELECT  EmployeeID, ManagerID, FirstName, LastName,
            0 AS Lvl
    FROM    HR.Employee
    WHERE   ManagerID IS NULL

    UNION ALL

    -- 2. Recursive member: joins the table to the rows produced by the previous step
    SELECT  e.EmployeeID, e.ManagerID, e.FirstName, e.LastName,
            o.Lvl + 1
    FROM    HR.Employee AS e
    JOIN    Org         AS o ON e.ManagerID = o.EmployeeID      -- refers to the CTE itself
)
SELECT  EmployeeID, ManagerID, FirstName, LastName, Lvl
FROM    Org
ORDER BY Lvl, EmployeeID;

How SQL Server evaluates it (logically):

The final result is the UNION ALL of every iteration: 1 + 4 + 4 + 7 = 16 rows. Each recursive step sees only the rows produced by the previous step, not the whole result so far.

B8. Rules and restrictions

• Anchor and recursive members are combined with UNION ALL.
• Column data types must match exactly between anchor and recursive member. String concatenation is the usual trap: the anchor’s FirstName + ' ' + LastName might be nvarchar(101) while the recursive member’s longer path is a different length. The error is Types don’t match between the anchor and the recursive part in column “Path” of recursive query “Org”. Fix it by casting both sides to the same type, e.g. CAST(… AS nvarchar(400)).
• The recursive member cannot use GROUP BY, HAVING, aggregate functions, DISTINCT, TOP, outer joins or subqueries. Do aggregation in the outer query.
• Recursion limit. By default SQL Server stops after 100 recursion levels with error 530: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. Add OPTION (MAXRECURSION n) to the outer statement: any value from 0 to 32,767; 0 means no limit. Use 0 only when the data is guaranteed to end — a cycle in the data (an employee who is, indirectly, their own manager) would otherwise run forever.

B9. Carrying values down the hierarchy: level, path and top ancestor

Columns computed in the anchor can be passed down and extended:

Sorting by Path lists a tree in “depth-first” order — each node immediately followed by its descendants — which is how people expect to read an organisation chart.

B10. Walking upwards

To list an employee’s chain of command, anchor on the employee and join to the manager at each step:

-- anchor:    WHERE EmployeeID = @EmployeeID
-- recursive: JOIN Chain AS c ON e.EmployeeID = c.ManagerID

The recursion ends when it reaches the person whose ManagerID is NULL: the join finds no further manager.

B11. Generating series

A recursive CTE can also generate rows from nothing — useful for a list of dates:

DECLARE @StartDate date = '2025-12-01', @EndDate date = '2025-12-31';

WITH Dates AS
(
    SELECT @StartDate AS TheDate
    UNION ALL
    SELECT DATEADD(DAY, 1, TheDate) FROM Dates WHERE TheDate < @EndDate
)
SELECT TheDate FROM Dates
OPTION (MAXRECURSION 400);       -- one recursion level per day after the first

DATEADD(DAY, 1, d) adds one day (Day 12 covers date functions). For long series, recursion is not the fastest method: SQL Server 2022 and later (compatibility level 160+) also provide the GENERATE_SERIES function, and Day 12 builds a permanent calendar table. Recursive generation is perfectly adequate for a few hundred rows.

C. Dataset and starting state

Category tree (products are attached to the lowest level only):

Accessories (9)            Camping (1)            Clothing (5)
├─ Backpacks (12)          ├─ Cooking (4)         ├─ Base Layers (8)
├─ Hydration (13) *empty*  ├─ Sleeping (3)        ├─ Footwear (7)
├─ Lighting (11)           └─ Tents (2)           └─ Jackets (6)
└─ Navigation (10)                                    ├─ Insulated Jackets (15)
                                                      └─ Waterproof Jackets (14)

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, 1, 15, 3.

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM HR.Employee)                               AS Employees,
        (SELECT COUNT(*) FROM HR.Employee WHERE ManagerID IS NULL)       AS TopOfHierarchy,
        (SELECT COUNT(*) FROM Inventory.Category)                        AS Categories,
        (SELECT COUNT(*) FROM Inventory.Category WHERE ParentCategoryID IS NULL) AS TopCategories;

D. Worked examples

Worked example 1 — Day 3’s “large orders” query, rewritten with one CTE

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
),
CustomerAvg AS                                   -- second CTE built on the first
(
    SELECT  CustomerID, AVG(OrderNet) AS AvgOrder
    FROM    OrderTotals
    GROUP BY CustomerID
)
SELECT  t.OrderID,
        t.CustomerID,
        t.OrderDate,
        CAST(t.OrderNet AS decimal(10,2)) AS OrderNet,
        CAST(a.AvgOrder AS decimal(10,2)) AS CustomerAvgOrder
FROM    OrderTotals AS t
JOIN    CustomerAvg AS a ON a.CustomerID = t.CustomerID
WHERE   t.OrderDate >= '2025-01-01'
  AND   t.OrderDate <  '2026-01-01'
  AND   t.OrderNet > 2 * a.AvgOrder
ORDER BY t.OrderDate, t.OrderID;

Expected result: the same 19 rows as Day 3 Task 3, from order 1177 to order 1376. The per-order calculation is now written once.

Worked example 2 — Month on month with a CTE self-join

WITH Monthly AS
(
    SELECT  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
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY MONTH(o.OrderDate)
)
SELECT  cur.MonthNo,
        CAST(cur.NetSales  AS decimal(12,2))                  AS NetSales,
        CAST(prev.NetSales AS decimal(12,2))                  AS PrevMonth,
        CAST(cur.NetSales - prev.NetSales AS decimal(12,2))   AS Change,
        CAST(100.0 * (cur.NetSales - prev.NetSales)
             / NULLIF(prev.NetSales, 0) AS decimal(6,1))      AS ChangePct
FROM    Monthly AS cur
LEFT JOIN Monthly AS prev ON prev.MonthNo = cur.MonthNo - 1
ORDER BY cur.MonthNo;

Expected result: 12 rows. January has NULL in the last three columns. Selected rows:

Business meaning: the August dip and September rebound are mostly the warehouse-migration week, not a change in demand. A percentage change on its own can mislead.

Worked example 3 — The organisation chart (recursive, top-down)

WITH Org AS
(
    SELECT  EmployeeID, ManagerID, JobTitle,
            CAST(CONCAT_WS(N' ', FirstName, LastName) AS nvarchar(400)) AS EmployeeName,
            0                                                            AS Lvl,
            CAST(CONCAT_WS(N' ', FirstName, LastName) AS nvarchar(400)) AS OrgPath
    FROM    HR.Employee
    WHERE   ManagerID IS NULL

    UNION ALL

    SELECT  e.EmployeeID, e.ManagerID, e.JobTitle,
            CAST(CONCAT_WS(N' ', e.FirstName, e.LastName) AS nvarchar(400)),
            o.Lvl + 1,
            CAST(o.OrgPath + N' > ' + CONCAT_WS(N' ', e.FirstName, e.LastName) AS nvarchar(400))
    FROM    HR.Employee AS e
    JOIN    Org         AS o ON e.ManagerID = o.EmployeeID
)
SELECT  EmployeeID,
        REPLICATE(N'    ', Lvl) + EmployeeName AS IndentedName,   -- 4 spaces per level
        JobTitle,
        Lvl,
        OrgPath
FROM    Org
ORDER BY OrgPath;

Expected result: 16 rows in depth-first order.

(In SSMS the indentation shows as spaces; the table above uses visible padding.) Remove the CASTs and run it again to see the Types don’t match error from B8.

Note: James Reid left in June 2025 but still appears; the hierarchy table records reporting lines, not employment status. Add WHERE TerminationDate IS NULL to both members if leavers should be hidden — filtering only the outer query would also hide any current employees who reported through a leaver.

Worked example 4 — Category paths

WITH CategoryTree AS
(
    SELECT  CategoryID, ParentCategoryID, CategoryName,
            1                                   AS Lvl,
            CAST(CategoryName AS nvarchar(400)) AS CategoryPath
    FROM    Inventory.Category
    WHERE   ParentCategoryID IS NULL

    UNION ALL

    SELECT  c.CategoryID, c.ParentCategoryID, c.CategoryName,
            t.Lvl + 1,
            CAST(t.CategoryPath + N' > ' + c.CategoryName AS nvarchar(400))
    FROM    Inventory.Category AS c
    JOIN    CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
)
SELECT  CategoryID, Lvl, CategoryPath
FROM    CategoryTree
ORDER BY CategoryPath;

Expected result: 15 rows, from Accessories and Accessories > Backpacks to Clothing > Jackets > Waterproof Jackets. Three rows have Lvl = 1, ten have Lvl = 2 and two have Lvl = 3.

Worked example 5 — Days in August 2025 with no orders (date series + LEFT JOIN)

DECLARE @StartDate date = '2025-08-01',
        @EndDate   date = '2025-08-31';

WITH Dates AS
(
    SELECT @StartDate AS TheDate
    UNION ALL
    SELECT DATEADD(DAY, 1, TheDate) FROM Dates WHERE TheDate < @EndDate
)
SELECT  d.TheDate
FROM    Dates AS d
LEFT JOIN Sales.SalesOrder AS o ON o.OrderDate = d.TheDate
WHERE   o.OrderID IS NULL                      -- anti-join from Day 3
ORDER BY d.TheDate
OPTION (MAXRECURSION 100);

Expected result: 21 dates, including the unbroken run 8–18 August. Ridgeway is a small business, so many quiet days are normal; the warehouse-migration week (11–17 August) sits inside a longer quiet spell. On Day 29 you will find runs like this automatically (“gaps and islands”).

Highlight the DECLARE together with the query when you run it — otherwise you get Must declare the scalar variable “@StartDate”.

Worked example 6 — Hitting the recursion limit

WITH Numbers AS
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 150
)
SELECT MAX(n) AS MaxN FROM Numbers;                         -- error 530 at level 100

WITH Numbers AS
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 150
)
SELECT MAX(n) AS MaxN FROM Numbers
OPTION (MAXRECURSION 200);                                  -- 150

Expected result: the first statement fails with error 530 (The maximum recursion 100 has been exhausted…); the second returns 150. Note that both statements are complete and end with semicolons, so they can be run together.

E. Guided practical lab — 2025 sales by top-level category

Products belong to the lowest-level categories (Waterproof Jackets, Tents…), but management wants 2025 net sales by the three top-level categories: Accessories, Camping and Clothing.

Step 1 — Build the category tree with each node’s top-level ancestor

1. Do: Write a recursive CTE that carries the top-level category’s ID and name down to every descendant.

2. Run:

   USE RetailLab;
   GO
   
   WITH CategoryTree AS
   (
       SELECT  CategoryID,
               CategoryName,
               CategoryID   AS TopCategoryID,
               CategoryName AS TopCategoryName,
               1            AS Lvl
       FROM    Inventory.Category
       WHERE   ParentCategoryID IS NULL
   
       UNION ALL
   
       SELECT  c.CategoryID,
               c.CategoryName,
               t.TopCategoryID,        -- passed down unchanged
               t.TopCategoryName,
               t.Lvl + 1
       FROM    Inventory.Category AS c
       JOIN    CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
   )
   SELECT  CategoryID, CategoryName, TopCategoryName, Lvl
   FROM    CategoryTree
   ORDER BY TopCategoryName, Lvl, CategoryName;

3. Observe: 15 rows. Waterproof Jackets shows TopCategoryName = Clothing and Lvl = 3.

4. Why it matters: This is a mapping from every category to its top level — it does not matter how deep a category sits.

5. Confirm: 15 rows: 5 with TopCategoryName = Accessories, 4 with Camping and 6 with Clothing (each top-level category counts as its own descendant).

   (No type mismatch here: CategoryName is passed through unchanged in both members, so its type is identical.)

Step 2 — Add a CTE for 2025 sales per product

1. Do: After the recursive CTE, add a comma and a second CTE.
2. Run: append to the WITH list (replace the final SELECT for now with a check on the new CTE):

   ,
   ProductSales AS
   (
       SELECT  l.ProductID,
               COUNT(DISTINCT o.OrderID)                            AS Orders,
               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
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
       GROUP BY l.ProductID
   )
   SELECT  COUNT(*) AS ProductsSold, SUM(Units) AS Units, CAST(SUM(NetSales) AS decimal(12,2)) AS NetSales
   FROM    ProductSales;

3. Observe: 28, 538, 63741.94.
4. Why it matters: The control total matches Day 2’s 2025 figure, so this CTE is correct before you build on it.
5. Confirm: 63741.94.

Step 3 — Roll sales up to the top-level category

1. Do: Replace the final SELECT with one that joins products to the tree.

2. Run: the complete statement:

   WITH CategoryTree AS
   (
       SELECT  CategoryID, CategoryName,
               CategoryID AS TopCategoryID, CategoryName AS TopCategoryName, 1 AS Lvl
       FROM    Inventory.Category
       WHERE   ParentCategoryID IS NULL
       UNION ALL
       SELECT  c.CategoryID, c.CategoryName, t.TopCategoryID, t.TopCategoryName, t.Lvl + 1
       FROM    Inventory.Category AS c
       JOIN    CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
   ),
   ProductSales AS
   (
       SELECT  l.ProductID,
               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
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
       GROUP BY l.ProductID
   )
   SELECT  t.TopCategoryName,
           SUM(ps.Units)                          AS Units,
           CAST(SUM(ps.NetSales) AS decimal(12,2)) AS NetSales
   FROM    ProductSales      AS ps
   JOIN    Inventory.Product AS p ON p.ProductID  = ps.ProductID
   JOIN    CategoryTree      AS t ON t.CategoryID = p.CategoryID
   GROUP BY t.TopCategoryName
   ORDER BY NetSales DESC, t.TopCategoryName;

3. Observe:

   TopCategoryName	Units	NetSales
   Camping	185	27572.05
   Clothing	211	24438.80
   Accessories	142	11731.09

4. Why it matters: The query does not hard-code any category IDs or levels. If Merchandising adds a fourth level tomorrow, it still works.

5. Confirm: 3 rows; the three NetSales values add up to 63741.94.

Step 4 — Add an automatic reconciliation check

1. Do: Add a third CTE holding the result, and return it together with a check row that compares the total with the control total.
2. Run: replace the final SELECT of step 3 with:

   , ByTop AS
   (
       SELECT  t.TopCategoryName, SUM(ps.NetSales) AS NetSales
       FROM    ProductSales      AS ps
       JOIN    Inventory.Product AS p ON p.ProductID  = ps.ProductID
       JOIN    CategoryTree      AS t ON t.CategoryID = p.CategoryID
       GROUP BY t.TopCategoryName
   )
   SELECT  CAST((SELECT SUM(NetSales) FROM ByTop)        AS decimal(12,2)) AS CategoryTotal,
           CAST((SELECT SUM(NetSales) FROM ProductSales) AS decimal(12,2)) AS ProductTotal,
           CASE WHEN (SELECT SUM(NetSales) FROM ByTop) = (SELECT SUM(NetSales) FROM ProductSales)
                THEN 'Reconciled' ELSE 'MISMATCH' END                       AS CheckResult;

   (Delete the final SELECT from step 3 first: a WITH list is followed by exactly one statement. Start the , ByTop AS directly after the closing parenthesis of ProductSales.)
3. Observe: 63741.94, 63741.94, Reconciled.
4. Why it matters: If a product ever sat in a category that the tree could not reach — for example, a category whose parent was deleted — its sales would vanish from the category report. A built-in reconciliation catches that immediately.
5. Confirm: Reconciled.

F. Independent practice

Task 1 — Best sellers of 2025 (simple CTE used twice)

Using one CTE that returns units sold per product in 2025 (non-cancelled orders), return ProductID, ProductCode, ProductName and Units for products whose units are above the average units per product (averaged over the products that sold in 2025). Sort by units descending, then ProductID.

Success criteria: 14 rows, from product 9 (COK-003, 38 units) to product 27 (BPK-002, 20 units). 28 products sold in 2025, averaging about 19.21 units.

Task 2 — Chain of command (recursive, upwards)

Using a variable @EmployeeID int = 13, return the chain of command from that employee up to the Managing Director: Step (0 for the employee), EmployeeID, EmployeeName and JobTitle, sorted by Step.

Success criteria: 4 rows — Marek Nowak (0), Gareth Price (1), Fiona Campbell (2), Eleanor Hughes (3). With @EmployeeID = 1 you get 1 row.

Task 3 — Everyone below each manager (recursive + aggregation)

For every employee who has at least one report (direct or indirect), return ManagerID, ManagerName, TeamSize (number of current employees anywhere below them) and TeamSalary (their total salary). Sort by TeamSize descending, then ManagerID.

Success criteria: 6 rows. Eleanor Hughes: 14 / 640000.00; Raj Patel: 6 / 213000.00; Liam O’Connor: 4 / 129500.00; Fiona Campbell: 3 / 91000.00; Gareth Price: 2 / 53000.00; Tom Baxter: 1 / 29000.00.

Task 4 — December trading calendar (date series + aggregation)

Return one row for every day of December 2025 with TheDate, Orders and NetSales (non-cancelled orders), showing 0 and 0.00 on days without sales. Use variables for the start and end dates.

Success criteria: 31 rows; 10 days have no orders; the totals of the two columns are 27 and 9718.65. 13 December (1 order, 1153.30) is the best single day.

Stretch challenge — Category tree with rolled-up figures

Produce the full category tree (15 rows) sorted by path, with an indented name and, for each category, the number of products in that category and all its descendants, plus 2025 net sales for that subtree. Empty categories show 0 and 0.00.

Success criteria: Clothing shows 11 products and 24438.80; Jackets 5 products and 15090.15; Hydration 0 and 0.00; the three top-level rows add up to 30 products and 63741.94.

G. Solutions and explanations

WITH Units2025 AS
(
    SELECT  l.ProductID, SUM(l.Quantity) AS Units
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY l.ProductID
)
SELECT  u.ProductID, p.ProductCode, p.ProductName, u.Units
FROM    Units2025         AS u
JOIN    Inventory.Product AS p ON p.ProductID = u.ProductID
WHERE   u.Units > (SELECT AVG(CAST(Units AS decimal(10,2))) FROM Units2025)
ORDER BY u.Units DESC, u.ProductID;

DECLARE @EmployeeID int = 13;

WITH Chain AS
(
    SELECT  0 AS Step, EmployeeID, ManagerID, JobTitle,
            CAST(CONCAT_WS(N' ', FirstName, LastName) AS nvarchar(101)) AS EmployeeName
    FROM    HR.Employee
    WHERE   EmployeeID = @EmployeeID

    UNION ALL

    SELECT  c.Step + 1, e.EmployeeID, e.ManagerID, e.JobTitle,
            CAST(CONCAT_WS(N' ', e.FirstName, e.LastName) AS nvarchar(101))
    FROM    HR.Employee AS e
    JOIN    Chain       AS c ON e.EmployeeID = c.ManagerID       -- move UP one level
)
SELECT  Step, EmployeeID, EmployeeName, JobTitle
FROM    Chain
ORDER BY Step;

WITH Descendants AS
(
    SELECT  EmployeeID AS RootID, EmployeeID       -- every employee is the root of their own subtree
    FROM    HR.Employee

    UNION ALL

    SELECT  d.RootID, e.EmployeeID
    FROM    HR.Employee  AS e
    JOIN    Descendants  AS d ON e.ManagerID = d.EmployeeID
)
SELECT  d.RootID                                AS ManagerID,
        CONCAT_WS(N' ', m.FirstName, m.LastName) AS ManagerName,
        COUNT(*)                                AS TeamSize,
        SUM(e.Salary)                           AS TeamSalary
FROM    Descendants AS d
JOIN    HR.Employee AS e ON e.EmployeeID = d.EmployeeID      -- the descendant
JOIN    HR.Employee AS m ON m.EmployeeID = d.RootID          -- the manager
WHERE   d.EmployeeID <> d.RootID                             -- exclude the manager themself
  AND   e.TerminationDate IS NULL                            -- current employees only
GROUP BY d.RootID, m.FirstName, m.LastName
ORDER BY TeamSize DESC, ManagerID;

DECLARE @StartDate date = '2025-12-01',
        @EndDate   date = '2025-12-31';

WITH Dates AS
(
    SELECT @StartDate AS TheDate
    UNION ALL
    SELECT DATEADD(DAY, 1, TheDate) FROM Dates WHERE TheDate < @EndDate
),
DailySales AS
(
    SELECT  o.OrderDate,
            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.OrderStatus <> 'Cancelled'
      AND   o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY o.OrderDate
)
SELECT  d.TheDate,
        COALESCE(s.Orders, 0)                               AS Orders,
        CAST(COALESCE(s.NetSales, 0) AS decimal(12,2))      AS NetSales
FROM    Dates AS d
LEFT JOIN DailySales AS s ON s.OrderDate = d.TheDate
ORDER BY d.TheDate
OPTION (MAXRECURSION 400);

WITH CategoryTree AS
(
    SELECT  CategoryID, CategoryName, 1 AS Lvl, CAST(CategoryName AS nvarchar(400)) AS CategoryPath
    FROM    Inventory.Category
    WHERE   ParentCategoryID IS NULL
    UNION ALL
    SELECT  c.CategoryID, c.CategoryName, t.Lvl + 1,
            CAST(t.CategoryPath + N' > ' + c.CategoryName AS nvarchar(400))
    FROM    Inventory.Category AS c
    JOIN    CategoryTree       AS t ON c.ParentCategoryID = t.CategoryID
),
SubTree AS                                   -- (root, descendant) pairs, including self
(
    SELECT  CategoryID AS RootID, CategoryID AS DescendantID
    FROM    Inventory.Category
    UNION ALL
    SELECT  s.RootID, c.CategoryID
    FROM    Inventory.Category AS c
    JOIN    SubTree            AS s ON c.ParentCategoryID = s.DescendantID
),
ProductSales AS
(
    SELECT  l.ProductID, 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.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
    GROUP BY l.ProductID
),
RolledUp AS
(
    SELECT  s.RootID,
            COUNT(p.ProductID)              AS Products,     -- counts only matched products
            COALESCE(SUM(ps.NetSales), 0)   AS NetSales
    FROM    SubTree AS s
    LEFT JOIN Inventory.Product AS p  ON p.CategoryID = s.DescendantID
    LEFT JOIN ProductSales      AS ps ON ps.ProductID = p.ProductID
    GROUP BY s.RootID
)
SELECT  t.CategoryID,
        REPLICATE(N'    ', t.Lvl - 1) + t.CategoryName AS Category,
        r.Products,
        CAST(r.NetSales AS decimal(12,2))               AS NetSales2025
FROM    CategoryTree AS t
JOIN    RolledUp     AS r ON r.RootID = t.CategoryID
ORDER BY t.CategoryPath;

H. Knowledge check

1. Understanding. In a recursive CTE, what does the recursive member see on each iteration: the entire result so far, or only the rows from the previous iteration?

2. Debugging. This fails with Incorrect syntax near the keyword ‘with’. Why?

   SELECT COUNT(*) FROM Sales.Customer
   WITH c AS (SELECT * FROM Sales.Customer) SELECT * FROM c;

3. Debugging. A recursive org-chart query fails with Types don’t match between the anchor and the recursive part in column “OrgPath”. Explain and fix.

4. Query interpretation. You run a CTE, then a second SELECT … FROM thatCte on the next line, highlighted together. The second statement fails with Invalid object name. Why?

5. Understanding. Why should you be careful with OPTION (MAXRECURSION 0) on a hierarchy that users can edit?

I. Troubleshooting and cleanup

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

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 reproduces Day 3’s 19 rows with the per-order logic written once.
• Your org chart (worked example 3) returns 16 rows and you have seen the Types don’t match error.
• The guided lab returns Camping 27572.05, Clothing 24438.80, Accessories 11731.09 and Reconciled.
• Tasks 1–4 match the success criteria (14, 4, 6 and 31 rows).
• You can explain what each recursion iteration receives as input.

Recap

• Variables (@name) hold one value for a batch; GO ends the batch.
• A CTE names a query for the next statement only. It is expanded, not stored.
• Multiple CTEs read top to bottom; a later CTE may use an earlier one; a CTE may be referenced several times, including in a self-join.
• A recursive CTE = anchor UNION ALL recursive member. Each iteration sees only the previous iteration’s rows. Types must match exactly.
• Carry level, path and top ancestor down the hierarchy; walk upwards by reversing the join.
• The default recursion limit is 100; set OPTION (MAXRECURSION n) on the outer statement, and beware of cycles with 0.

Next: Day 5 — Window functions part 1: ranking with ROW_NUMBER, RANK, DENSE_RANK and NTILE — including the clean way to solve “largest order per customer”.

References (Microsoft Learn)

• WITH common_table_expression (Transact-SQL) — syntax, recursion rules and restrictions
• Recursive queries using common table expressions
• Query hints (Transact-SQL) — MAXRECURSION
• DECLARE @local_variable (Transact-SQL)
• SET @local_variable (Transact-SQL)
• Batches of SQL statements
• GENERATE_SERIES (Transact-SQL)