Day 6: Window functions, part 2

A. Lesson overview

Activity breakdown

Business scenario. Finance wants a monthly KPI report: each month’s sales next to the previous month, the same month last year, a year-to-date total and a three-month moving average. Customer Services wants to know how often good customers reorder. Marketing wants to know how few customers make up most of the revenue. All of these compare a row with its neighbours or accumulate values in order — the job of offset functions and framed aggregates.

B. Theory explained clearly

Part 1 — LAG and LEAD

B1. Looking backwards and forwards

LAG  ( expression [, offset [, default]] ) OVER ( [PARTITION BY …] ORDER BY … )
LEAD ( expression [, offset [, default]] ) OVER ( [PARTITION BY …] ORDER BY … )

ORDER BY inside OVER is required: “previous” only means something in a defined order. Make the order unique, or the “previous” row among ties is arbitrary.

USE RetailLab;
GO

SELECT  OrderID,
        OrderDate,
        LAG(OrderDate)  OVER (ORDER BY OrderDate, OrderID) AS PreviousOrderDate,
        LEAD(OrderDate) OVER (ORDER BY OrderDate, OrderID) AS NextOrderDate
FROM    Sales.SalesOrder
WHERE   CustomerID = 1
ORDER BY OrderDate, OrderID;

The first row’s PreviousOrderDate and the last row’s NextOrderDate are NULL. LAG(x, 12) fetches the value twelve rows back — on monthly data, the same month last year. LAG(x, 1, 0) returns 0 instead of NULL for the first row; the default must be compatible with the expression’s type.

Compared with Day 4’s self-join (ON prev.MonthNo = cur.MonthNo - 1), LAG is shorter and reads the data once. The self-join is still useful when “previous” is defined by a value rather than a position — for example when a month is missing, LAG returns the previous row, which may be two months earlier.

B2. DATEDIFF

DATEDIFF(DAY, start, end) returns the number of day boundaries crossed between two dates as an int — for date values, simply the number of days. DATEDIFF(DAY, '2025-12-28', '2025-12-29') is 1. Day 12 covers its behaviour with months and years, which is less obvious.

Part 2 — Running totals and window frames

B3. A running total

Adding ORDER BY to an aggregate’s OVER clause makes it cumulative:

SELECT  OrderID, OrderDate,
        SUM(ShippingCost) OVER (ORDER BY OrderDate, OrderID
                                ROWS UNBOUNDED PRECEDING) AS RunningShipping
FROM    Sales.SalesOrder
ORDER BY OrderDate, OrderID;

Each row’s value is the sum from the first row up to and including the current row. With PARTITION BY YEAR(OrderDate), the total restarts each year — a year-to-date figure.

B4. Window frames

The frame is the subset of the partition that an aggregate uses for the current row. Its full syntax:

{ ROWS | RANGE } BETWEEN <start> AND <end>

<start>/<end> ::= UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

B5. ROWS versus RANGE

• ROWS counts physical rows. Two rows with the same ORDER BY value are still separate rows.
• RANGE works on ORDER BY values: all rows that tie with the current row are its “peers”, and CURRENT ROW includes all of them. In SQL Server, RANGE only supports UNBOUNDED and CURRENT ROW boundaries — not n PRECEDING.

Order 1354 and order 1355 were both placed on 11 December 2025:

With RANGE, both 11 December orders see each other as “current”, so both show 6. Neither result is wrong — RANGE answers “total up to the end of this date”, ROWS answers “total up to this row” — but you must choose deliberately.

B6. The default frame

If an aggregate’s OVER clause has ORDER BY but no frame, SQL Server uses

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This produces running totals that “jump” over ties, as above. It can also be slower: SQL Server’s RANGE frames for running aggregates typically use a worktable in tempdb, whereas ROWS UNBOUNDED PRECEDING can usually be computed in memory. Always write the frame explicitly — normally ROWS — when you use ORDER BY in an aggregate window.

ROW_NUMBER, RANK, LAG, LEAD and the other ranking and offset functions do not accept a frame.

Part 3 — Moving averages

AVG(MonthlySales) OVER (ORDER BY Yr, Mth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

The first two rows have fewer than three rows in their frame, so they are averages of one and two values. Either show them and say so, or hide them by also computing COUNT(*) OVER (same frame) and returning NULL when it is below 3. ROWS counts rows, not months: if a month were missing, the “3-month” average would silently span four months. Day 12’s calendar table fixes that by guaranteeing one row per month.

Part 4 — FIRST_VALUE and LAST_VALUE

FIRST_VALUE(x) returns x from the first row of the frame; LAST_VALUE(x) from the last row of the frame.

The trap. With ORDER BY and no frame, the default frame ends at CURRENT ROW (B6). So LAST_VALUE returns the current row’s own value (or its last peer) — almost never what you want. Specify the whole partition:

LAST_VALUE(ProductName) OVER (PARTITION BY CustomerID
                              ORDER BY OrderDate, OrderLineID
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

FIRST_VALUE is not affected because the default frame already starts at the beginning. An equally good alternative to LAST_VALUE is FIRST_VALUE with the order reversed: FIRST_VALUE(x) OVER (PARTITION BY … ORDER BY OrderDate DESC, OrderLineID DESC).

Part 5 — Compute before you filter

Window functions only see rows that survive WHERE. If you filter to 2025 and then use LAG, the first 2025 order’s “previous order” is NULL, although the customer ordered in December 2024. When the previous row may lie outside your filter, compute the window function in a CTE over all relevant rows, then filter in the outer query.

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

USE RetailLab;
GO

SELECT  COUNT(DISTINCT DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1)) AS MonthsWithOrders,
        (SELECT CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(12,2))
         FROM Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
         WHERE o.OrderStatus <> 'Cancelled')                                AS NetSalesAllTime
FROM    Sales.SalesOrder;

(DATEFROMPARTS(y, m, d) builds a date from numbers; here it gives the first day of each order’s month.)

D. Worked examples

All examples that use monthly figures start from the same CTE:

WITH Monthly AS
(
    SELECT  YEAR(o.OrderDate)  AS Yr,
            MONTH(o.OrderDate) AS Mth,
            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'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
)

Worked example 1 — Previous month and same month last year (LAG)

WITH Monthly AS
(
    SELECT  YEAR(o.OrderDate) AS Yr, MONTH(o.OrderDate) AS Mth,
            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'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
)
SELECT  Yr, Mth,
        CAST(NetSales AS decimal(12,2))                                   AS NetSales,
        CAST(LAG(NetSales)     OVER (ORDER BY Yr, Mth) AS decimal(12,2))  AS PrevMonth,
        CAST(LAG(NetSales, 12) OVER (ORDER BY Yr, Mth) AS decimal(12,2))  AS SameMonthLastYear
FROM    Monthly
ORDER BY Yr, Mth;

Expected result: 24 rows. PrevMonth is NULL only for January 2024; SameMonthLastYear is NULL for all of 2024. For example, December 2025 shows 9718.65, 4088.04 and 9343.79. This relies on every month having at least one order — true here; Day 12 shows how to guarantee it.

Worked example 2 — Days between a customer’s orders (LAG + DATEDIFF)

SELECT  OrderID,
        OrderDate,
        LAG(OrderDate) OVER (ORDER BY OrderDate, OrderID)                         AS PrevOrderDate,
        DATEDIFF(DAY, LAG(OrderDate) OVER (ORDER BY OrderDate, OrderID), OrderDate) AS DaysSincePrev,
        LEAD(OrderDate) OVER (ORDER BY OrderDate, OrderID)                        AS NextOrderDate
FROM    Sales.SalesOrder
WHERE   CustomerID = 1
  AND   OrderStatus <> 'Cancelled'
ORDER BY OrderDate, OrderID;

Expected result: 27 rows. The first (order 1010, 2024-01-18) has no previous order; the longest gap is 86 days (before order 1061 on 2024-05-30); the last two orders are one day apart (1372 on 2025-12-28 and 1373 on 2025-12-29).

Worked example 3 — Average reorder interval per customer

WITH Gaps AS
(
    SELECT  CustomerID,
            DATEDIFF(DAY,
                     LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID),
                     OrderDate) AS DaysSincePrev
    FROM    Sales.SalesOrder
    WHERE   OrderStatus <> 'Cancelled'
)
SELECT  CustomerID,
        COUNT(DaysSincePrev)                                                   AS Intervals,
        AVG(DaysSincePrev)                                                     AS AvgDaysInteger,  -- trap!
        CAST(AVG(CAST(DaysSincePrev AS decimal(10,2))) AS decimal(6,1))        AS AvgDays,
        MIN(DaysSincePrev)                                                     AS ShortestGap,
        MAX(DaysSincePrev)                                                     AS LongestGap
FROM    Gaps
GROUP BY CustomerID
HAVING  COUNT(DaysSincePrev) >= 10
ORDER BY AvgDays, CustomerID;

Expected result: 11 customers with at least 10 intervals. First rows: customer 18 (26 intervals, AvgDaysInteger 25, AvgDays 25.6), customer 12 (27, 27.2), customer 1 (27, 27.3). Last: customer 8 (10 intervals, 70, 70.9).

What to notice: DATEDIFF returns int, so AVG truncates (Day 2’s trap again). COUNT(DaysSincePrev) ignores each customer’s first order, whose gap is NULL. A gap of 0 means two orders on the same day.

Worked example 4 — Year-to-date running total (ROWS)

WITH Monthly AS
(
    SELECT  YEAR(o.OrderDate) AS Yr, MONTH(o.OrderDate) AS Mth,
            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'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
)
SELECT  Yr, Mth,
        CAST(NetSales AS decimal(12,2)) AS NetSales,
        CAST(SUM(NetSales) OVER (PARTITION BY Yr ORDER BY Mth
                                 ROWS UNBOUNDED PRECEDING) AS decimal(12,2)) AS YearToDate
FROM    Monthly
ORDER BY Yr, Mth;

Expected result: 24 rows. YearToDate restarts in January 2025 (3749.57) and ends each year at the annual total: 58612.94 in December 2024 and 63741.94 in December 2025.

Worked example 5 — ROWS versus RANGE with tied dates

SELECT  OrderID,
        OrderDate,
        COUNT(*) OVER (ORDER BY OrderDate)                                 AS DefaultFrame,   -- RANGE
        COUNT(*) OVER (ORDER BY OrderDate RANGE UNBOUNDED PRECEDING)       AS RangeFrame,
        COUNT(*) OVER (ORDER BY OrderDate, OrderID ROWS UNBOUNDED PRECEDING) AS RowsFrame
FROM    Sales.SalesOrder
WHERE   OrderDate >= '2025-12-01'
ORDER BY OrderDate, OrderID;

Expected result: 27 rows. The first eight:

The default frame behaves exactly like RANGE. All three columns end at 27.

Worked example 6 — Three-month moving average

WITH Monthly AS
(
    SELECT  YEAR(o.OrderDate) AS Yr, MONTH(o.OrderDate) AS Mth,
            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'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
)
SELECT  Yr, Mth,
        CAST(NetSales AS decimal(12,2)) AS NetSales,
        COUNT(*) OVER (ORDER BY Yr, Mth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MonthsInWindow,
        CAST(AVG(NetSales) OVER (ORDER BY Yr, Mth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
             AS decimal(12,2))           AS MovingAvg3
FROM    Monthly
ORDER BY Yr, Mth;

Expected result: 24 rows. January 2024: window of 1 month, average 3477.28. February 2024: 2 months, 3805.20. From March 2024 onwards, 3 months. December 2025: 6148.23. Note that the window crosses the year boundary — no PARTITION BY here, deliberately.

Worked example 7 — First and last product a customer bought

WITH CustomerLines AS
(
    SELECT  o.CustomerID, o.OrderDate, l.OrderLineID, p.ProductName
    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'
)
SELECT DISTINCT
        CustomerID,
        FIRST_VALUE(ProductName) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderLineID) AS FirstProduct,
        LAST_VALUE(ProductName)  OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderLineID) AS LastProduct_Wrong,
        LAST_VALUE(ProductName)  OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderLineID
                                       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastProduct
FROM    CustomerLines
WHERE   CustomerID IN (1, 2, 3)
ORDER BY CustomerID;

Expected result: the LastProduct_Wrong column produces many rows per customer, because it returns a different value on each line (each row’s own product), so DISTINCT cannot collapse them. Remove that column and you get 3 rows:

DISTINCT is acceptable here because the two correct window columns have one value per customer. A GROUP BY CustomerID cannot be used with these window functions directly, because ProductName is not grouped.

E. Guided practical lab — Monthly KPI report

Finance wants one 24-row report with: net sales, orders, previous month, month-on-month %, same month last year, year-on-year %, year-to-date, 3-month moving average and the month’s share of its year.

Step 1 — Monthly base figures

1. Do: Create the base CTE with sales and order counts.
2. Run:

   USE RetailLab;
   GO
   
   WITH Monthly AS
   (
       SELECT  YEAR(o.OrderDate)                                    AS Yr,
               MONTH(o.OrderDate)                                   AS Mth,
               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'
       GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
   )
   SELECT Yr, Mth, Orders, CAST(NetSales AS decimal(12,2)) AS NetSales
   FROM   Monthly
   ORDER BY Yr, Mth;

3. Observe: 24 rows. January 2024: 12 orders, 3477.28.
4. Why it matters: Window functions will run on these 24 rows, not on the 689 order lines.
5. Confirm: 24 rows.

Step 2 — Add the comparison columns with a second CTE

1. Do: Add a Calc CTE that holds the window results (unrounded), so the final SELECT can do arithmetic on them.

2. Run: replace the final SELECT with:

   , Calc AS
   (
       SELECT  Yr, Mth, Orders, NetSales,
               LAG(NetSales)     OVER (ORDER BY Yr, Mth)                                       AS PrevMonth,
               LAG(NetSales, 12) OVER (ORDER BY Yr, Mth)                                       AS LastYear,
               SUM(NetSales)     OVER (PARTITION BY Yr ORDER BY Mth ROWS UNBOUNDED PRECEDING)  AS YTD,
               AVG(NetSales)     OVER (ORDER BY Yr, Mth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MA3,
               COUNT(*)          OVER (ORDER BY Yr, Mth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MA3Months,
               SUM(NetSales)     OVER (PARTITION BY Yr)                                        AS YearTotal
       FROM    Monthly
   )
   SELECT  Yr, Mth, Orders,
           CAST(NetSales AS decimal(12,2))                                                 AS NetSales,
           CAST(PrevMonth AS decimal(12,2))                                                AS PrevMonth,
           CAST(100.0 * (NetSales - PrevMonth) / NULLIF(PrevMonth, 0) AS decimal(6,1))     AS MoMPct,
           CAST(LastYear AS decimal(12,2))                                                 AS LastYear,
           CAST(100.0 * (NetSales - LastYear)  / NULLIF(LastYear, 0)  AS decimal(6,1))     AS YoYPct,
           CAST(YTD AS decimal(12,2))                                                      AS YTD,
           CASE WHEN MA3Months = 3 THEN CAST(MA3 AS decimal(12,2)) END                      AS MovingAvg3,
           CAST(100.0 * NetSales / YearTotal AS decimal(5,1))                              AS PctOfYear
   FROM    Calc
   ORDER BY Yr, Mth;

   (Start , Calc AS immediately after the closing parenthesis of Monthly, and delete step 1’s final SELECT.)

3. Observe: 24 rows. The 2025 rows:

   Mth	Orders	NetSales	PrevMonth	MoMPct	LastYear	YoYPct	YTD	MovingAvg3	PctOfYear
   1	13	3749.57	9343.79	-59.9	3477.28	7.8	3749.57	6669.55	5.9
   2	12	3891.60	3749.57	3.8	4133.11	-5.8	7641.17	5661.65	6.1
   3	18	6887.29	3891.60	77.0	4015.35	71.5	14528.46	4842.82	10.8
   4	17	6867.62	6887.29	-0.3	5232.15	31.3	21396.08	5882.17	10.8
   5	17	5310.48	6867.62	-22.7	2715.47	95.6	26706.57	6355.13	8.3
   6	20	5879.70	5310.48	10.7	5189.27	13.3	32586.27	6019.27	9.2
   7	20	4536.58	5879.70	-22.8	6324.53	-28.3	37122.85	5242.26	7.1
   8	10	2419.30	4536.58	-46.7	5873.15	-58.8	39542.15	4278.53	3.8
   9	15	5755.09	2419.30	137.9	2365.67	143.3	45297.25	4236.99	9.0
   10	13	4638.00	5755.09	-19.4	3027.87	53.2	49935.25	4270.80	7.3
   11	16	4088.04	4638.00	-11.9	6915.27	-40.9	54023.29	4827.05	6.4
   12	27	9718.65	4088.04	137.7	9343.79	4.0	63741.94	6148.23	15.2

4. Why it matters: Every percentage uses unrounded inputs; NULLIF guards the divisions; MovingAvg3 is NULL for the first two months of 2024 instead of showing a misleading partial average.

5. Confirm: December 2025’s YTD equals 63741.94 and its PctOfYear column for 2025 adds to about 100 (rounding may give 99.9–100.1).

Step 3 — Read the report

1. Do: Answer from the output: Which 2025 month had the best year-on-year growth, and why should that number be treated with caution?
2. Run: No new SQL.
3. Observe: September 2025: +143.3% against September 2024 — but September 2024 was itself unusually weak (8 orders), and August 2025 was depressed by the warehouse migration, pushing some demand into September.
4. Why it matters: Window functions make comparisons easy to compute; interpreting them still needs business context.
5. Confirm: You can explain the September figure in one sentence.

F. Independent practice

Task 1 — Customer 1’s 2025 orders with the true previous order (compute before filter)

For customer 1, list each non-cancelled 2025 order with OrderID, OrderDate, PrevOrderDate and DaysSincePrev. The first 2025 order must show the gap from the customer’s last 2024 order.

Success criteria: 14 rows. Order 1178 (2025-01-10) shows previous date 2024-12-01 and 40 days. The final row, order 1373, shows 1 day.

Task 2 — December 2025 cash received, running total

Using Sales.Payment, return one row per payment date in December 2025 with Payments (count), AmountReceived and RunningTotal. Sort by date.

Success criteria: 17 rows. 3 December: 615.60 / 615.60. 31 December: 707.00 / 8831.07. 24 payments in total.

Task 3 — First and latest product per customer (all customers)

For every customer who has ordered, return CustomerID, FirstProduct (first ever, by order date then order line) and LatestProduct, plus a column SameProduct showing Yes or No. Sort by CustomerID.

Success criteria: 35 rows. Customer 35: first Kids Daypack 12L, latest Dreamer 3-Season Sleeping Bag. Exactly 3 customers show Yes.

Task 4 — Three increases in a row (challenging)

Find orders whose net value is higher than the customer’s previous order, which was higher than the order before it, which was higher than the order before that — i.e. the fourth order in a strictly rising sequence of four. Return CustomerID, OrderID, OrderDate and the four values Net3Back, Net2Back, Net1Back, NetThis (2 decimal places). Use non-cancelled orders; sort by OrderDate, OrderID.

Success criteria: 11 rows. The first is customer 6, order 1025 (2024-03-01): 53.10 → 170.10 → 178.20 → 622.80. Customer 2 appears three times, including two overlapping sequences in December 2025.

Stretch challenge — How concentrated is revenue? (Pareto)

Rank customers by lifetime net sales (non-cancelled) and show Position, CustomerID, LifetimeNet, CumulativeNet and CumulativePct. Then answer, with a query: how many customers account for the first 50% of revenue, and how many for 80%?

Success criteria: 35 rows; position 9 (customer 23) is the first to reach 50% (52.8%); position 18 (customer 25) the first to reach 80% (80.1%). So about a quarter of ordering customers generate half the revenue.

G. Solutions and explanations

WITH AllOrders AS
(
    SELECT  OrderID, OrderDate,
            LAG(OrderDate) OVER (ORDER BY OrderDate, OrderID) AS PrevOrderDate
    FROM    Sales.SalesOrder
    WHERE   CustomerID = 1
      AND   OrderStatus <> 'Cancelled'          -- no year filter here
)
SELECT  OrderID, OrderDate, PrevOrderDate,
        DATEDIFF(DAY, PrevOrderDate, OrderDate) AS DaysSincePrev
FROM    AllOrders
WHERE   OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
ORDER BY OrderDate, OrderID;

SELECT  PaymentDate,
        COUNT(*)                                                         AS Payments,
        SUM(Amount)                                                      AS AmountReceived,
        SUM(SUM(Amount)) OVER (ORDER BY PaymentDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM    Sales.Payment
WHERE   PaymentDate >= '2025-12-01' AND PaymentDate < '2026-01-01'
GROUP BY PaymentDate
ORDER BY PaymentDate;

WITH CustomerLines AS
(
    SELECT  o.CustomerID, o.OrderDate, l.OrderLineID, p.ProductName
    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'
),
FirstLast AS
(
    SELECT DISTINCT
            CustomerID,
            FIRST_VALUE(ProductName) OVER (PARTITION BY CustomerID
                                           ORDER BY OrderDate, OrderLineID) AS FirstProduct,
            FIRST_VALUE(ProductName) OVER (PARTITION BY CustomerID
                                           ORDER BY OrderDate DESC, OrderLineID DESC) AS LatestProduct
    FROM    CustomerLines
)
SELECT  CustomerID, FirstProduct, LatestProduct,
        CASE WHEN FirstProduct = LatestProduct THEN 'Yes' ELSE 'No' END AS SameProduct
FROM    FirstLast
ORDER BY CustomerID;

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
),
WithHistory AS
(
    SELECT  *,
            LAG(OrderNet, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS Net1Back,
            LAG(OrderNet, 2) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS Net2Back,
            LAG(OrderNet, 3) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID) AS Net3Back
    FROM    OrderTotals
)
SELECT  CustomerID, OrderID, OrderDate,
        CAST(Net3Back AS decimal(10,2)) AS Net3Back,
        CAST(Net2Back AS decimal(10,2)) AS Net2Back,
        CAST(Net1Back AS decimal(10,2)) AS Net1Back,
        CAST(OrderNet AS decimal(10,2)) AS NetThis
FROM    WithHistory
WHERE   OrderNet > Net1Back AND Net1Back > Net2Back AND Net2Back > Net3Back
ORDER BY OrderDate, OrderID;

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
),
Pareto AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY LifetimeNet DESC, CustomerID)                    AS Position,
            CustomerID,
            LifetimeNet,
            SUM(LifetimeNet) OVER (ORDER BY LifetimeNet DESC, CustomerID ROWS UNBOUNDED PRECEDING) AS CumulativeNet,
            SUM(LifetimeNet) OVER ()                                                     AS GrandTotal
    FROM    CustomerSales
)
SELECT  Position, CustomerID,
        CAST(LifetimeNet   AS decimal(12,2))                         AS LifetimeNet,
        CAST(CumulativeNet AS decimal(12,2))                         AS CumulativeNet,
        CAST(100.0 * CumulativeNet / GrandTotal AS decimal(5,1))     AS CumulativePct
FROM    Pareto
ORDER BY Position;

-- The two answers
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
),
Pareto AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY LifetimeNet DESC, CustomerID) AS Position,
            100.0 * SUM(LifetimeNet) OVER (ORDER BY LifetimeNet DESC, CustomerID ROWS UNBOUNDED PRECEDING)
                  / SUM(LifetimeNet) OVER ()                         AS CumulativePct
    FROM    CustomerSales
)
SELECT  MIN(CASE WHEN CumulativePct >= 50 THEN Position END) AS CustomersFor50Pct,
        MIN(CASE WHEN CumulativePct >= 80 THEN Position END) AS CustomersFor80Pct,
        COUNT(*)                                             AS OrderingCustomers
FROM    Pareto;

H. Knowledge check

1. Understanding. What frame does SQL Server use for SUM(x) OVER (ORDER BY d) when no frame is specified, and what goes wrong when d has duplicates?

2. Query interpretation. What does LAG(NetSales, 12, 0) OVER (ORDER BY Yr, Mth) return for March 2024 in the monthly data, and why might 0 be a poor default?

3. Debugging. LAST_VALUE(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) returns each row’s own date. Why? Give two fixes.

4. Debugging. A LAG over 2025 orders shows NULL for the first 2025 order of every customer, although most ordered in 2024. Why, and how do you fix it?

5. Understanding. Why can a “3-month moving average” computed with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW cover more than three calendar months?

I. Troubleshooting and cleanup

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

J. Completion checklist, recap and references

Completion checklist

• Worked example 5 shows 6, 6 versus 5, 6 for the two orders on 11 December 2025.
• Your KPI report (lab step 2) shows December 2025: 9718.65, MoM 137.7%, YoY 4.0%, YTD 63741.94.
• Task 1 shows 40 days for customer 1’s first 2025 order.
• Tasks 2–4 match the success criteria (17, 35 and 11 rows).
• You always write an explicit ROWS frame when an aggregate’s OVER has ORDER BY.

Recap

• LAG/LEAD fetch values from previous/next rows; offset and default are optional; order must be unique.
• An aggregate with ORDER BY in OVER is cumulative. Without an explicit frame, SQL Server uses RANGE … CURRENT ROW, which treats ties as one step and can be slower.
• ROWS counts rows; RANGE groups peers and supports only UNBOUNDED/CURRENT ROW boundaries in SQL Server.
• Moving windows: ROWS BETWEEN n PRECEDING AND CURRENT ROW; watch partial windows and missing periods.
• LAST_VALUE needs a full-partition frame; FIRST_VALUE with reversed order is an alternative.
• Compute windows before filtering when neighbours may lie outside the filter.

Next: Day 7 — Week 1 review and integration, plus logical query processing order, TOP and OFFSET … FETCH.

References (Microsoft Learn)

• SELECT – OVER clause (Transact-SQL) — frames, ROWS vs RANGE, default frame
• LAG (Transact-SQL)
• LEAD (Transact-SQL)
• FIRST_VALUE (Transact-SQL)
• LAST_VALUE (Transact-SQL)
• Analytic functions (Transact-SQL)
• DATEDIFF (Transact-SQL)
• DATEFROMPARTS (Transact-SQL)