Day 29: Real-world scenarios, part 1

A. Lesson overview

Activity breakdown

Business scenario. The finance director asks four questions for the year-end pack: Who owes us money at 31 December, and for how long? What did the receivables balance look like at the end of last year? Which customers buy from us month after month? and What do people buy together? — plus the operations manager’s question: When were there no orders at all?

B. Theory explained clearly

Part 1 — A method

Before writing SQL for a business question, write down:

The last step matters most: an answer that reconciles to an independent total is far more likely to be right.

Part 2 — Running balances

A ledger lists amounts in date order; the running balance is the cumulative sum:

SUM(Amount) OVER (PARTITION BY CustomerID
                  ORDER BY EntryDate, EntrySeq, DocumentID
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

• PARTITION BY restarts the sum per customer.
• The ORDER BY must be unique (add tie-breakers): with ties, results are not deterministic.
• Write ROWS explicitly. The default frame when ORDER BY is present is RANGE … CURRENT ROW, which treats ties as one group and is slower (Day 6).

A balance as at a date is just SUM(Amount) for entries up to that date — no window needed.

Part 3 — Gaps and islands

An island is a run of consecutive values (days, months, numbers) with no gaps; a gap is the space between islands.

Islands — the difference technique. For values that should step by 1, subtract a row number: within a consecutive run, value − row_number is constant.

Group by the difference to get each island’s start, end and length. For dates, use DATEADD(DAY, -ROW_NUMBER() OVER (…), d) as the key; for months, convert to a month number such as YEAR(d) * 12 + MONTH(d).

Gaps — LEAD. Compare each value with the next one; where the difference is more than one step, there’s a gap from value + 1 to next − 1. Alternatively, use a calendar table and NOT EXISTS to list every missing day.

Part 4 — Market basket measures

For products A and B, over N orders:

Generate pairs with a self-join on the order, a.ProductID < b.ProductID to count each pair once. On small data, treat these numbers as hints, not findings — five orders aren’t a trend.

C. Dataset and starting state

Setup. Lab.Calendar must exist (Day 12 script). Then run scripts/lessons/day29_customer_ledger.sql (download it) — it drops and recreates Lab.CustomerLedger only.

Validation. The script ends with this query. Expected: 764, 369, 395, 122503.33, -120861.55, 1641.78.

SELECT  COUNT(*)                                                    AS LedgerRows,
        SUM(CASE WHEN EntryType = 'Invoice' THEN 1 ELSE 0 END)      AS Invoices,
        SUM(CASE WHEN EntryType = 'Payment' THEN 1 ELSE 0 END)      AS Payments,
        SUM(CASE WHEN EntryType = 'Invoice' THEN Amount ELSE 0 END) AS InvoicedTotal,
        SUM(CASE WHEN EntryType = 'Payment' THEN Amount ELSE 0 END) AS PaidTotal,
        SUM(Amount)                                                 AS Outstanding
FROM    Lab.CustomerLedger;

PaidTotal equals the setup-validation payments total (120,861.55). SELECT COUNT(*) FROM Lab.Calendar; must return 1096.

D. Worked examples

Worked example 1 — A customer statement

SELECT  EntryDate, EntryType, DocumentID, OrderID, Amount,
        SUM(Amount) OVER (PARTITION BY CustomerID
                          ORDER BY EntryDate, EntrySeq, DocumentID
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Balance
FROM    Lab.CustomerLedger
WHERE   CustomerID = 1
ORDER BY EntryDate, EntrySeq, DocumentID
OFFSET 48 ROWS FETCH NEXT 6 ROWS ONLY;          -- the last 6 of customer 1's 54 entries

Expected result:

The window is calculated before OFFSET … FETCH, so the balances include all earlier entries. Customer 1 pays on the day of each order — until the two pending orders at the end of December.

Worked example 2 — Islands: consecutive months with orders

WITH CustomerMonth AS
(
    SELECT DISTINCT CustomerID, YEAR(OrderDate) * 12 + MONTH(OrderDate) - 1 AS MonthIndex
    FROM   Sales.SalesOrder
    WHERE  OrderStatus <> 'Cancelled'
),
Keyed AS
(
    SELECT  CustomerID, MonthIndex,
            MonthIndex - ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY MonthIndex) AS IslandKey
    FROM    CustomerMonth
),
Islands AS
(
    SELECT  CustomerID, MIN(MonthIndex) AS FirstMonth, MAX(MonthIndex) AS LastMonth, COUNT(*) AS Months
    FROM    Keyed
    GROUP BY CustomerID, IslandKey
)
SELECT TOP (3)
        CustomerID,
        DATEFROMPARTS(FirstMonth / 12, FirstMonth % 12 + 1, 1) AS StreakStart,
        DATEFROMPARTS(LastMonth  / 12, LastMonth  % 12 + 1, 1) AS StreakEndMonth,
        Months
FROM    Islands
ORDER BY Months DESC, CustomerID;

Expected result: 1 2024-05-01 2025-02-01 10, 12 2025-05-01 2025-12-01 8, 18 2024-10-01 2025-03-01 6. Customer 12 is still on a streak at year end — a loyalty target. Run SELECT COUNT(*) FROM Islands (replace the final SELECT) to see that 35 customers form 176 islands.

Worked example 3 — Gaps: stretches with no orders

WITH OrderDays AS
(
    SELECT DISTINCT OrderDate FROM Sales.SalesOrder
    WHERE  OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
),
NextDay AS
(
    SELECT OrderDate, LEAD(OrderDate) OVER (ORDER BY OrderDate) AS NextOrderDate
    FROM   OrderDays
)
SELECT  DATEADD(DAY, 1, OrderDate)                    AS GapStart,
        DATEADD(DAY, -1, NextOrderDate)               AS GapEnd,
        DATEDIFF(DAY, OrderDate, NextOrderDate) - 1   AS DaysWithoutOrders
FROM    NextDay
WHERE   DATEDIFF(DAY, OrderDate, NextOrderDate) - 1 >= 5
ORDER BY GapStart;

Expected result: 8 gaps of five days or more:

The query counts any order, including cancelled ones — an order was still placed. This LEAD approach can’t see a gap at the very start of the year (before the first order); the calendar approach in task 1 can.

Worked example 4 — Products bought together

WITH OrderProduct AS
(
    SELECT DISTINCT l.OrderID, p.ProductCode
    FROM   Sales.SalesOrderLine AS l
    JOIN   Sales.SalesOrder     AS o ON o.OrderID   = l.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    WHERE  o.OrderStatus <> 'Cancelled'
),
Totals AS (SELECT COUNT(DISTINCT OrderID) * 1.0 AS N FROM OrderProduct),
Single AS (SELECT ProductCode, COUNT(*) AS Orders FROM OrderProduct GROUP BY ProductCode),
Pairs  AS
(
    SELECT a.ProductCode AS ProductA, b.ProductCode AS ProductB, COUNT(*) AS Together
    FROM   OrderProduct AS a
    JOIN   OrderProduct AS b ON b.OrderID = a.OrderID AND a.ProductCode < b.ProductCode
    GROUP BY a.ProductCode, b.ProductCode
)
SELECT TOP (3) WITH TIES
        p.ProductA, p.ProductB, p.Together,
        CAST(100.0 * p.Together / t.N         AS decimal(5,2)) AS SupportPct,
        CAST(100.0 * p.Together / sa.Orders   AS decimal(5,1)) AS ConfidenceAtoB_Pct,
        CAST((p.Together / t.N) / ((sa.Orders / t.N) * (sb.Orders / t.N)) AS decimal(5,2)) AS Lift
FROM    Pairs  AS p
CROSS JOIN Totals AS t
JOIN    Single AS sa ON sa.ProductCode = p.ProductA
JOIN    Single AS sb ON sb.ProductCode = p.ProductB
ORDER BY p.Together DESC;

Expected result: 3 rows, each bought together in 5 of the 369 non-cancelled orders (support 1.36%):

(Row order among the tied rows may vary — add p.ProductA to the ORDER BY to fix it.) Lifts above 1 suggest genuine affinity — base layers with waterproof jackets, footwear with insulated jackets, two tents — but five orders is a small sample.

E. Guided practical lab — Year-end receivables

Step 1 — Who owes us at year end?

1. Do: Sum the ledger per customer up to 2025-12-31; keep non-zero balances.
2. Run:

   USE RetailLab;
   GO
   SELECT  l.CustomerID, c.FirstName, c.LastName, SUM(l.Amount) AS Balance
   FROM    Lab.CustomerLedger AS l
   JOIN    Sales.Customer     AS c ON c.CustomerID = l.CustomerID
   WHERE   l.EntryDate <= '2025-12-31'
   GROUP BY l.CustomerID, c.FirstName, c.LastName
   HAVING  SUM(l.Amount) <> 0
   ORDER BY Balance DESC, l.CustomerID;

3. Observe: 1 Alice Morgan 830.69, 13 Mohammed Rahman 662.15, 17 … 129.00, 15 … 19.94 — 4 customers, total 1,641.78.
4. Why it matters: No customer has a negative (credit) balance — worth checking explicitly: HAVING SUM(l.Amount) < 0 returns nothing.
5. Confirm: the four balances add up to the script’s Outstanding value.

Step 2 — Which orders are unpaid?

1. Do: Net invoices and payments per order.
2. Run:

   SELECT  OrderID, CustomerID,
           MIN(CASE WHEN EntryType = 'Invoice' THEN EntryDate END) AS OrderDate,
           SUM(CASE WHEN EntryType = 'Invoice' THEN Amount ELSE 0 END) AS Invoiced,
           -SUM(CASE WHEN EntryType = 'Payment' THEN Amount ELSE 0 END) AS Paid,
           SUM(Amount) AS Unpaid
   FROM    Lab.CustomerLedger
   GROUP BY OrderID, CustomerID
   HAVING  SUM(Amount) <> 0
   ORDER BY OrderDate, OrderID;

3. Observe: 5 orders: 1365 (customer 17, 2025-12-23, 129.00), 1371 (13, 2025-12-27, 662.15), 1372 (1, 2025-12-28, 147.59), 1373 (1, 2025-12-29, 683.10), 1374 (15, 2025-12-30, 19.94) — each completely unpaid.
4. Why it matters: Two views of the same money — by customer and by order — must agree: 129.00 + 662.15 + 147.59 + 683.10 + 19.94 = 1,641.78.
5. Confirm: the total of Unpaid is 1641.78.

Step 3 — How long outstanding: calendar days and working days

1. Do: Age each unpaid order at 2025-12-31 using Lab.Calendar for working days (as Day 18: days after the order date up to and including the as-at date).
2. Run:

   WITH Unpaid AS
   (
       SELECT OrderID, CustomerID, MIN(CASE WHEN EntryType = 'Invoice' THEN EntryDate END) AS OrderDate,
              SUM(Amount) AS Unpaid
       FROM   Lab.CustomerLedger
       GROUP BY OrderID, CustomerID
       HAVING SUM(Amount) <> 0
   )
   SELECT  u.OrderID, u.CustomerID, u.OrderDate, u.Unpaid,
           DATEDIFF(DAY, u.OrderDate, '2025-12-31') AS CalendarDays,
           w.WorkingDays
   FROM    Unpaid AS u
   CROSS APPLY (SELECT COUNT(*) AS WorkingDays
                FROM   Lab.Calendar AS c
                WHERE  c.CalendarDate > u.OrderDate AND c.CalendarDate <= '2025-12-31'
                  AND  c.IsWorkingDay = 1) AS w
   ORDER BY u.OrderDate;

3. Observe: CalendarDays / WorkingDays: 1365 8 / 4, 1371 4 / 3, 1372 3 / 3, 1373 2 / 2, 1374 1 / 1.
4. Why it matters: Order 1365 is eight calendar days old but only four working days — Christmas, Boxing Day and a weekend fall in between. Payment terms are usually in working or calendar days by contract; the calendar table makes either easy.
5. Confirm: all five orders are inside a 30-day term.

Step 4 — The balance a year earlier

1. Do: Re-run step 1 as at 2024-12-31.
2. Run: change '2025-12-31' to '2024-12-31' in step 1.
3. Observe: one customer: 23 … 56.05.
4. Why it matters: “As at” queries let finance reproduce any past period-end figure from the ledger. (Find the order: SELECT * FROM Lab.CustomerLedger WHERE CustomerID = 23 AND EntryDate BETWEEN '2024-12-01' AND '2025-01-31' ORDER BY EntryDate, EntrySeq; — an invoice in December 2024 paid in January 2025.)
5. Confirm: 1 row.

Step 5 — Reconcile with the source tables

1. Do: Check the ledger against Sales directly.
2. Run:

   SELECT  (SELECT SUM(Amount) FROM Sales.Payment)                                   AS PaymentsInSales,
           (SELECT -SUM(Amount) FROM Lab.CustomerLedger WHERE EntryType = 'Payment')  AS PaymentsInLedger,
           (SELECT COUNT(*) FROM Sales.SalesOrder WHERE OrderStatus <> 'Cancelled')   AS LiveOrders,
           (SELECT COUNT(*) FROM Lab.CustomerLedger WHERE EntryType = 'Invoice')      AS InvoicesInLedger;

3. Observe: 120861.55, 120861.55, 369, 369.
4. Why it matters: A derived table is only as trustworthy as its reconciliation. Make this part of every report you build.
5. Confirm: both pairs match.

F. Independent practice

Task 1 — Islands of order days, with the calendar

Using Lab.Calendar for 2025: (a) how many days had at least one order; (b) the longest run of consecutive calendar days that all had orders; (c) how many days had no orders.

Success criteria: (a) 159; (b) 2025-12-19 to 2025-12-31, 13 days; (c) 206.

Task 2 — Confidence for one product

For orders containing TNT-002 (non-cancelled), which products appear most often alongside it, and with what confidence?

Success criteria: 31 orders contain TNT-002. Five products tie at 3 orders each (confidence 9.7%): BPK-002, BSL-002, COK-001, FTW-003, NAV-001.

Task 3 — Cohorts and repeat custom

Group customers by the quarter of their first non-cancelled order. For each cohort, count customers and how many ordered again in a later quarter.

Success criteria: 2024-Q1 19 / 19, 2024-Q2 8 / 8, 2024-Q3 5 / 5, 2024-Q4 1 / 1, 2025-Q1 1 / 1, 2025-Q4 1 / 0 — 35 customers. Write one sentence on why this dataset makes retention look perfect (hint: when did most customers join, and how long have they had to return?).

Task 4 — Split payments (challenging)

Find orders paid with more than one payment. For each combination of payment methods, count orders.

Success criteria: 31 orders; Card + Gift Card 13, Gift Card + PayPal 11, Bank Transfer + Gift Card 7. Build the combination label with STRING_AGG(PaymentMethod, ' + ') WITHIN GROUP (ORDER BY PaymentMethod).

Stretch challenge — First-in, first-out allocation

Allocate each customer’s total payments to their invoices oldest first (FIFO), ignoring which order each payment named. Which invoices remain open, and by how much? Compare with step 2 of the lab.

Success criteria: FIFO open invoices: 1371 (13) 662.15, 1372 (1) 147.59, 1373 (1) 683.10, 1374 (15) 19.94 — and 1376 (customer 17) with 129.00 open of 707.00, instead of order 1365. Total still 1,641.78. Explain the difference.

G. Solutions and explanations

WITH Days AS
(
    SELECT  c.CalendarDate,
            CASE WHEN EXISTS (SELECT 1 FROM Sales.SalesOrder AS o WHERE o.OrderDate = c.CalendarDate)
                 THEN 1 ELSE 0 END AS HasOrder
    FROM    Lab.Calendar AS c
    WHERE   c.CalendarDate >= '2025-01-01' AND c.CalendarDate < '2026-01-01'
),
Keyed AS
(
    SELECT  CalendarDate,
            DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY CalendarDate), CalendarDate) AS IslandKey
    FROM    Days
    WHERE   HasOrder = 1
)
SELECT  (SELECT SUM(HasOrder) FROM Days)      AS DaysWithOrders,
        (SELECT COUNT(*) - SUM(HasOrder) FROM Days) AS DaysWithoutOrders,
        i.RunStart, i.RunEnd, i.RunDays
FROM   (SELECT TOP (1) MIN(CalendarDate) AS RunStart, MAX(CalendarDate) AS RunEnd, COUNT(*) AS RunDays
        FROM   Keyed GROUP BY IslandKey ORDER BY RunDays DESC, RunStart) AS i;

WITH OrderProduct AS
(
    SELECT DISTINCT l.OrderID, p.ProductCode
    FROM   Sales.SalesOrderLine AS l
    JOIN   Sales.SalesOrder     AS o ON o.OrderID = l.OrderID
    JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
    WHERE  o.OrderStatus <> 'Cancelled'
),
WithTent AS (SELECT OrderID FROM OrderProduct WHERE ProductCode = 'TNT-002')
SELECT  op.ProductCode, COUNT(*) AS OrdersTogether,
        (SELECT COUNT(*) FROM WithTent) AS OrdersWithTNT002,
        CAST(100.0 * COUNT(*) / (SELECT COUNT(*) FROM WithTent) AS decimal(5,1)) AS ConfidencePct
FROM    OrderProduct AS op
JOIN    WithTent     AS w ON w.OrderID = op.OrderID
WHERE   op.ProductCode <> 'TNT-002'
GROUP BY op.ProductCode
ORDER BY OrdersTogether DESC, op.ProductCode
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

WITH Live AS
(
    SELECT CustomerID, OrderDate,
           CONCAT(YEAR(OrderDate), '-Q', DATEPART(QUARTER, OrderDate)) AS OrderQuarter
    FROM   Sales.SalesOrder WHERE OrderStatus <> 'Cancelled'
),
FirstQ AS
(
    SELECT CustomerID, MIN(OrderQuarter) AS Cohort FROM Live GROUP BY CustomerID
)
SELECT  f.Cohort,
        COUNT(*) AS Customers,
        SUM(CASE WHEN EXISTS (SELECT 1 FROM Live AS l
                              WHERE l.CustomerID = f.CustomerID AND l.OrderQuarter > f.Cohort)
                 THEN 1 ELSE 0 END) AS OrderedAgainLater
FROM    FirstQ AS f
GROUP BY f.Cohort
ORDER BY f.Cohort;

WITH Split AS
(
    SELECT  OrderID,
            STRING_AGG(PaymentMethod, ' + ') WITHIN GROUP (ORDER BY PaymentMethod) AS Methods
    FROM    Sales.Payment
    GROUP BY OrderID
    HAVING  COUNT(*) > 1
)
SELECT Methods, COUNT(*) AS Orders
FROM   Split
GROUP BY Methods
ORDER BY Orders DESC, Methods;

WITH Inv AS
(
    SELECT  CustomerID, OrderID, EntryDate, Amount,
            SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY EntryDate, OrderID
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumAfter
    FROM    Lab.CustomerLedger
    WHERE   EntryType = 'Invoice'
),
Paid AS
(
    SELECT CustomerID, -SUM(Amount) AS TotalPaid
    FROM   Lab.CustomerLedger WHERE EntryType = 'Payment'
    GROUP BY CustomerID
)
SELECT  i.CustomerID, i.OrderID, i.EntryDate, i.Amount,
        CASE WHEN i.CumAfter <= COALESCE(p.TotalPaid, 0) THEN 0
             WHEN i.CumAfter - i.Amount >= COALESCE(p.TotalPaid, 0) THEN i.Amount
             ELSE i.CumAfter - COALESCE(p.TotalPaid, 0) END AS OpenAmount
FROM    Inv  AS i
LEFT JOIN Paid AS p ON p.CustomerID = i.CustomerID
WHERE   i.CumAfter > COALESCE(p.TotalPaid, 0)
ORDER BY i.CustomerID, i.EntryDate, i.OrderID;

H. Knowledge check

1. Understanding. Why must the ORDER BY inside a running-total window be unique, and what goes wrong otherwise?

2. Query interpretation. In the islands technique, why does MonthIndex - ROW_NUMBER() stay constant within a run of consecutive months?

3. Debugging. A running balance query with ORDER BY EntryDate and no frame clause gives the same balance on two rows of the same day, and the first of them looks wrong. Explain.

4. Understanding. Why does worked example 3 (gaps with LEAD) miss a gap at the start of the year, and how does a calendar table fix it?

5. Understanding. A pair of products has confidence 60% but lift 0.9. Is the pair a good cross-selling suggestion?

I. Troubleshooting and cleanup

Cleanup. Keep Lab.CustomerLedger and Lab.Calendar for Day 30. Nothing else was created.

J. Completion checklist, recap and references

Completion checklist

• Customer 1’s statement ended at 830.69.
• Longest monthly streaks: customers 1 (10), 12 (8), 18 (6).
• Eight gaps of five days or more in 2025, the longest 11 days in August.
• Receivables: 4 customers, 5 orders, 1,641.78 in total, reconciled to Sales.
• Tasks 1–4 match the success criteria.

Recap

• Write down grain, filters, definitions, edge cases and a reconciliation before writing SQL.
• Running balance: SUM() OVER (PARTITION BY … ORDER BY <unique> ROWS UNBOUNDED PRECEDING); “as at” is a filtered SUM.
• Islands: value − ROW_NUMBER() as a group key; gaps: LEAD/LAG or a calendar with NOT EXISTS.
• Calendar tables turn “working days” and “missing days” into simple joins.
• Market-basket: support, confidence, lift — and caution with small samples.
• Allocation rules (per invoice vs FIFO) change which invoice is overdue, not how much is owed.

Next: Day 30 — Real-world scenarios part 2 and the final assessment: ETL from staging, a Type 2 slowly changing dimension, DMV-based monitoring, and a final project with rubric.

References (Microsoft Learn)

• SELECT – OVER clause (Transact-SQL)
• LEAD (Transact-SQL)
• ROW_NUMBER (Transact-SQL)
• STRING_AGG (Transact-SQL)
• DATEADD (Transact-SQL)
• ORDER BY clause: OFFSET and FETCH (Transact-SQL)