<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Advanced querying
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 31 of 54 18 minAdvanced

Running totals, moving averages, LAG and LEAD

Aggregates as window functions: totals and shares beside each row, running totals, the frame (ROWS versus RANGE), moving averages, comparing with the previous or next row using LAG and LEAD, and the LAST_VALUE trap.

SUM, COUNT, AVG, MIN and MAX can all be used with OVER. Then they do not collapse the rows: each row gets the aggregate of its window. With PARTITION BY, the window is the row’s group:

Query.sql
SELECT Name, CategoryId, Stock,       SUM(Stock) OVER (PARTITION BY CategoryId) AS CategoryStock,       CAST(100.0 * Stock / SUM(Stock) OVER (PARTITION BY CategoryId)            AS decimal(5,1)) AS PctOfCategoryFROM dbo.ProductsWHERE CategoryId <= 3ORDER BY CategoryId, ProductId;
Output
Name                 CategoryId  Stock  CategoryStock  PctOfCategory
-------------------  ----------  -----  -------------  -------------
Claw hammer                   1     40            117           34.2
Cordless drill                1     12            117           10.3
Tape measure 8 m              1     65            117           55.6
Cement 25 kg                  2    300            520           57.7
Sand 25 kg                    2    220            520           42.3
Timber 2x4 2.4 m              3    500            580           86.2
Plywood sheet 18 mm           3     80            580           13.8

With GROUP BY you would get the totals or the products, not both. Here every product keeps its row and can be compared with its group, so a percentage of the total is one expression.

Running totals

The examples below use each order’s total, from a CTE like the one in the CTE lesson (cancelled orders left out). Add ORDER BY inside OVER and the window changes: each row sees only the rows up to itself. That is a running total:

Query.sql
WITH t AS (    SELECT o.OrderId, o.OrderDate, SUM(oi.Quantity * oi.UnitPrice) AS Total    FROM dbo.Orders AS o    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId    WHERE o.Status <> 'Cancelled'    GROUP BY o.OrderId, o.OrderDate)SELECT OrderId, OrderDate, Total,       SUM(Total) OVER (ORDER BY OrderDate                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalFROM tORDER BY OrderDate;
Output
OrderId  OrderDate    Total  RunningTotal
-------  ----------  ------  ------------
      1  2024-02-03   38.00         38.00
      2  2024-02-10  113.00        151.00
      3  2024-03-01  112.80        263.80
      4  2024-03-15  524.00        787.80
      5  2024-04-02  120.50        908.30
      7  2024-05-05  199.50       1107.80
      8  2024-05-18  102.50       1210.30
      9  2024-06-01   18.00       1228.30
     10  2024-06-12   91.35       1319.65

The frame

The part after ORDER BY is the frame: which rows of the partition, counted from the current row, go into the aggregate.

FrameRows in the window
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfrom the first row to this one: a running total
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWthis row and the two before it: a moving window of 3
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGthe row before, this row and the row after
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGthe whole partition (the same as no ORDER BY)
Interactive

Which rows does the frame see?

Current row:
SUM(Total) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
38.00 + 113.00 + 112.80 + 524.00 = 787.80
OrderIdOrderDateTotalSUM OVER (…)
112024-02-0338.0038.00
222024-02-10113.00151.00
332024-03-01112.80263.80
442024-03-15524.00787.80
552024-04-02120.50908.30
672024-05-05199.501107.80
782024-05-18102.501210.30
892024-06-0118.001228.30
9102024-06-1291.351319.65
Row 4 sees every row from the first up to itself (4 rows). Step down and the frame grows by one each time: a running total.

ROWS versus RANGE: the default frame

If you write ORDER BY in OVER and no frame, SQL Server uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE counts by value, not by position: every row with the same ORDER BY value is “the current row”. With ties, the running total jumps a whole group at a time. Here the orders are numbered by month:

Query.sql
WITH t AS (    SELECT o.OrderId, o.OrderDate, SUM(oi.Quantity * oi.UnitPrice) AS Total    FROM dbo.Orders AS o    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId    WHERE o.Status <> 'Cancelled'    GROUP BY o.OrderId, o.OrderDate)SELECT OrderId, MONTH(OrderDate) AS Mon, Total,       SUM(Total) OVER (ORDER BY MONTH(OrderDate)) AS DefaultRange,       SUM(Total) OVER (ORDER BY MONTH(OrderDate), OrderId                        ROWS UNBOUNDED PRECEDING) AS RowsFrameFROM tORDER BY Mon, OrderId;
Output
OrderId  Mon   Total  DefaultRange  RowsFrame
-------  ---  ------  ------------  ---------
      1    2   38.00        151.00      38.00
      2    2  113.00        151.00     151.00
      3    3  112.80        787.80     263.80
      4    3  524.00        787.80     787.80
      5    4  120.50        908.30     908.30
      7    5  199.50       1210.30    1107.80
      8    5  102.50       1210.30    1210.30
      9    6   18.00       1319.65    1228.30
     10    6   91.35       1319.65    1319.65

Both orders in February get 151.00 under RANGE, because they share the value 2. ROWS goes one row at a time. ROWS UNBOUNDED PRECEDING is short for “… AND CURRENT ROW”.

Moving averages

A moving average smooths out jumps by averaging each value with its neighbours. Here each order is averaged with the two before it. The first two rows have fewer than three rows in their frame, so they average what there is:

Query.sql
WITH t AS (    SELECT o.OrderId, o.OrderDate, SUM(oi.Quantity * oi.UnitPrice) AS Total    FROM dbo.Orders AS o    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId    WHERE o.Status <> 'Cancelled'    GROUP BY o.OrderId, o.OrderDate)SELECT OrderId, Total,       COUNT(*)   OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS InFrame,       AVG(Total) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3FROM tORDER BY OrderDate;
Output
OrderId   Total  InFrame  MovingAvg3
-------  ------  -------  ----------
      1   38.00        1   38.000000
      2  113.00        2   75.500000
      3  112.80        3   87.933333
      4  524.00        3  249.933333
      5  120.50        3  252.433333
      7  199.50        3  281.333333
      8  102.50        3  140.833333
      9   18.00        3  106.666666
     10   91.35        3   70.616666

LAG and LEAD: the row before and the row after

LAG(col) returns the value of col from the previous row in the window’s order, and LEAD(col) from the next one. There is no frame to think about. This compares every order with the one before:

Query.sql
WITH t AS (    SELECT o.OrderId, o.OrderDate, SUM(oi.Quantity * oi.UnitPrice) AS Total    FROM dbo.Orders AS o    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId    WHERE o.Status <> 'Cancelled'    GROUP BY o.OrderId, o.OrderDate)SELECT OrderId, OrderDate, Total,       LAG(Total) OVER (ORDER BY OrderDate)         AS PrevTotal,       Total - LAG(Total) OVER (ORDER BY OrderDate) AS Change,       DATEDIFF(day, LAG(OrderDate) OVER (ORDER BY OrderDate), OrderDate) AS DaysSincePrev,       LEAD(OrderId) OVER (ORDER BY OrderDate)      AS NextOrderFROM tORDER BY OrderDate;
Output
OrderId  OrderDate    Total  PrevTotal   Change  DaysSincePrev  NextOrder
-------  ----------  ------  ---------  -------  -------------  ---------
      1  2024-02-03   38.00       NULL     NULL           NULL          2
      2  2024-02-10  113.00      38.00    75.00              7          3
      3  2024-03-01  112.80     113.00    -0.20             20          4
      4  2024-03-15  524.00     112.80   411.20             14          5
      5  2024-04-02  120.50     524.00  -403.50             18          7
      7  2024-05-05  199.50     120.50    79.00             33          8
      8  2024-05-18  102.50     199.50   -97.00             13          9
      9  2024-06-01   18.00     102.50   -84.50             14         10
     10  2024-06-12   91.35      18.00    73.35             11       NULL

The first row has no previous row, so LAG returns NULL; the last has no next row, so LEAD does. Two optional arguments change that: LAG(col, offset, default). LAG(Total, 1, 0) looks one row back and gives 0 instead of NULL; LAG(Total, 3) looks three rows back.

With PARTITION BY, LAG stays inside the group. How long did each repeat customer wait between orders?

Query.sql
SELECT CustomerId, OrderId, OrderDate,       LAG(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS PrevOrder,       DATEDIFF(day,                LAG(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate),                OrderDate) AS DaysBetweenFROM dbo.OrdersWHERE CustomerId IN (1, 2, 4, 6)ORDER BY CustomerId, OrderDate;
Output
CustomerId  OrderId  OrderDate   PrevOrder   DaysBetween
----------  -------  ----------  ----------  -----------
         1        1  2024-02-03  NULL               NULL
         1        3  2024-03-01  2024-02-03           27
         2        2  2024-02-10  NULL               NULL
         2        9  2024-06-01  2024-02-10          112
         4        4  2024-03-15  NULL               NULL
         4        7  2024-05-05  2024-03-15           51
         6        5  2024-04-02  NULL               NULL
         6       10  2024-06-12  2024-04-02           71

FIRST_VALUE and the LAST_VALUE trap

FIRST_VALUE(col) and LAST_VALUE(col) return col from the first and last row of the frame. That word matters. With ORDER BY and no frame, the frame ends at the current row, so LAST_VALUE just returns the current row:

Query.sql
SELECT CategoryId, Name, Price,       FIRST_VALUE(Name) OVER (PARTITION BY CategoryId ORDER BY Price) AS Cheapest,       LAST_VALUE(Name)  OVER (PARTITION BY CategoryId ORDER BY Price) AS Dearest_Wrong,       LAST_VALUE(Name)  OVER (PARTITION BY CategoryId ORDER BY Price                               ROWS BETWEEN UNBOUNDED PRECEDING                                        AND UNBOUNDED FOLLOWING)  AS DearestFROM dbo.ProductsWHERE CategoryId IN (1, 2)ORDER BY CategoryId, Price;
Output
CategoryId  Name              Price  Cheapest          Dearest_Wrong     Dearest
----------  ----------------  -----  ----------------  ----------------  --------------
         1  Tape measure 8 m   9.75  Tape measure 8 m  Tape measure 8 m  Cordless drill
         1  Claw hammer       18.50  Tape measure 8 m  Claw hammer       Cordless drill
         1  Cordless drill    89.00  Tape measure 8 m  Cordless drill    Cordless drill
         2  Sand 25 kg         4.10  Sand 25 kg        Sand 25 kg        Cement 25 kg
         2  Cement 25 kg       7.20  Sand 25 kg        Cement 25 kg      Cement 25 kg

Naming a window (SQL Server 2022)

When several functions share one window, SQL Server 2022 lets you define it once with a WINDOW clause, after HAVING and before ORDER BY. OVER w uses it as it is; OVER (w ROWS …) adds a frame to it. ROW_NUMBER must use the plain version, because ranking functions do not accept a frame. Here the Tools products are taken in the order they were added:

Query.sql
SELECT Name, AddedOn, Stock, Price,       ROW_NUMBER() OVER w                          AS Pos,       SUM(Stock)   OVER (w ROWS UNBOUNDED PRECEDING) AS StockSoFar,       MIN(Price)   OVER (w ROWS UNBOUNDED PRECEDING) AS LowestSoFarFROM dbo.ProductsWHERE CategoryId = 1WINDOW w AS (PARTITION BY CategoryId ORDER BY AddedOn, ProductId)ORDER BY Pos;
Output
Name              AddedOn     Stock  Price  Pos  StockSoFar  LowestSoFar
----------------  ----------  -----  -----  ---  ----------  -----------
Claw hammer       2023-01-15     40  18.50    1          40        18.50
Tape measure 8 m  2023-01-15     65   9.75    2         105         9.75
Cordless drill    2023-02-01     12  89.00    3         117         9.75
Quick check

A query uses AVG(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). How many rows are averaged for the very first row?

Quick check

LAST_VALUE(Name) OVER (ORDER BY Price) returns the current row’s Name on every row. Why?

Practice

For every order that is not cancelled, show the customer’s first name, the OrderId, the order total and that customer’s running total of spending, in date order. Sort by first name, then date.

Show a hint

Start from the order totals CTE with CustomerId added. Then SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate ROWS UNBOUNDED PRECEDING).

Show the solution
Solution.sql
WITH t AS (    SELECT o.OrderId, o.CustomerId, o.OrderDate,           SUM(oi.Quantity * oi.UnitPrice) AS Total    FROM dbo.Orders AS o    JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId    WHERE o.Status <> 'Cancelled'    GROUP BY o.OrderId, o.CustomerId, o.OrderDate)SELECT c.FirstName, t.OrderId, t.Total,       SUM(t.Total) OVER (PARTITION BY t.CustomerId ORDER BY t.OrderDate                          ROWS UNBOUNDED PRECEDING) AS CustomerRunningFROM tJOIN dbo.Customers AS c ON c.CustomerId = t.CustomerIdORDER BY c.FirstName, t.OrderDate;
Output
FirstName  OrderId   Total  CustomerRunning
---------  -------  ------  ---------------
Asha             1   38.00            38.00
Asha             3  112.80           150.80
Ben              2  113.00           113.00
Ben              9   18.00           131.00
Dev              4  524.00           524.00
Dev              7  199.50           723.50
Farid            5  120.50           120.50
Farid           10   91.35           211.85
Zoë              8  102.50           102.50

Remember

  • SUM, COUNT, AVG, MIN and MAX with OVER keep every row and add the aggregate of its window.
  • PARTITION BY alone: the whole group. Add ORDER BY: the frame stops at the current row.
  • The frame (ROWS BETWEEN … AND …) picks which rows around the current one count.
  • ORDER BY with no frame means RANGE … CURRENT ROW: ties are added together. Write ROWS for running totals.
  • A moving average is AVG over ROWS BETWEEN n PRECEDING AND CURRENT ROW.
  • LAG and LEAD read the previous and next row; LAG(col, offset, default) avoids NULLs.
  • LAST_VALUE needs a frame to UNBOUNDED FOLLOWING, or use FIRST_VALUE with the order reversed.
  • SQL Server 2022 can name a shared window with the WINDOW clause.
0 of 54 lessons completed