Window Functions 2: Running Totals, ROWS vs RANGE Frames, LAG/LEAD and Gaps and Islands

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, CU10 (Cumulative Update 10)) against the series' Bookstore database; PostgreSQL comparisons were tried on PostgreSQL 18.6.

Guess First

We sort the orders between 20 and 23 December 2025 by date and print "how many orders have arrived so far". There are two orders on 21 December, four on 22 December and one on 23 December. Which numbers does the running_orders column show?

SELECT CAST(ordered_at AS date) AS order_date,
       order_id,
       COUNT(*) OVER (ORDER BY CAST(ordered_at AS date)) AS running_orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-20' AND ordered_at < '2025-12-24'
ORDER BY order_id;

If you expect 1, 2, 3, 4, 5, 6, 7 you are not alone. The real output:

order_date order_id running_orders
---------- -------- --------------
2025-12-21      247              2
2025-12-21      248              2
2025-12-22      249              6
2025-12-22      250              6
2025-12-22      251              6
2025-12-22      252              6
2025-12-23      253              7

Both orders on 21 December show 2, all four on 22 December show 6. This is not a bug but the default behaviour defined by the standard: when you write ORDER BY without specifying a frame, the window's bounds become RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats every row with the same ordering value as the "current row". This post covers the side of window functions that moves the window row by row: frames, running totals, moving averages, LAG/LEAD, medians, and gaps and islands.

The Default Frame: RANGE UNBOUNDED PRECEDING

In the previous post we split the window with PARTITION BY and numbered rows with the ranking functions. Aggregate window functions (SUM, COUNT, AVG, MIN, MAX) go one step further: when given an ORDER BY, for each row they look at a subset of the partition, the frame. A frame is defined in two ways:

• ROWS: by physical row count. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is exactly three rows.
• RANGE: by ordering value. CURRENT ROW means every row with the same ordering value.

The OVER documentation on Microsoft Learn states it plainly: with ORDER BY and without ROWS/RANGE, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In the opening query the ordering was by date only, so all orders of the same day fell into one "current row" group. The fix is to write the frame explicitly as ROWS and make the ordering unique:

SELECT CAST(ordered_at AS date) AS order_date,
       order_id,
       COUNT(*) OVER (ORDER BY CAST(ordered_at AS date), order_id
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_orders
FROM dbo.orders
WHERE ordered_at >= '2025-12-20' AND ordered_at < '2025-12-24'
ORDER BY order_id;

order_date order_id running_orders
---------- -------- --------------
2025-12-21      247              1
2025-12-21      248              2
2025-12-22      249              3
2025-12-22      250              4
2025-12-22      251              5
2025-12-22      252              6
2025-12-23      253              7

Memorise the rule: if you use an aggregate with OVER (ORDER BY ...), always write the frame explicitly. There may be cases where you really want the same day's orders to count as one step (an end-of-day balance, for example); then write RANGE deliberately so the reader knows it is intended.

Running Totals

The most common frame query is the year-to-date (YTD (Year to Date)) total. We compute the months first and then take a running total over them:

WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1) AS month_start,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2025-01-01' AND o.ordered_at < '2026-01-01'
    GROUP BY DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1)
)
SELECT month_start, revenue,
       SUM(revenue) OVER (ORDER BY month_start ROWS UNBOUNDED PRECEDING) AS ytd_revenue
FROM monthly
ORDER BY month_start;

month_start  revenue ytd_revenue
----------- -------- -----------
2025-01-01   8959.20     8959.20
2025-02-01  13727.50    22686.70
2025-03-01  20367.60    43054.30
2025-04-01  18488.20    61542.50
2025-05-01  13679.60    75222.10
2025-06-01  15414.30    90636.40
2025-07-01  12866.70   103503.10
2025-08-01  12681.50   116184.60
2025-09-01  33349.23   149533.83
2025-10-01  27489.90   177023.73
2025-11-01  20325.80   197349.53
2025-12-01  41866.20   239215.73

ROWS UNBOUNDED PRECEDING is the short form of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without window functions you would do this either with a correlated subquery (summing earlier months for every month, at a cost that grows with the square of the row count) or with a loop in the application. If you want a separate YTD per country, adding PARTITION BY country_code is enough; the total starts from zero in each partition.

The Performance Difference Between ROWS and RANGE

Even when the result is the same, ROWS and RANGE do not cost the same. I took a running total over order_items with a unique key (order_item_id) and compared the two frames with SET STATISTICS IO ON. Because the ordering key is unique, both queries produce the same result:

SET STATISTICS IO ON;
SELECT SUM(x.running) AS checksum
FROM (SELECT SUM(oi.quantity) OVER (ORDER BY oi.order_item_id) AS running
      FROM dbo.order_items AS oi) AS x;
SET STATISTICS IO OFF;

checksum
--------
  281845
Table 'Worktable'. Scan count 681, logical reads 4087, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'order_items'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SET STATISTICS IO ON;
SELECT SUM(x.running) AS checksum
FROM (SELECT SUM(oi.quantity) OVER (ORDER BY oi.order_item_id ROWS UNBOUNDED PRECEDING) AS running
      FROM dbo.order_items AS oi) AS x;
SET STATISTICS IO OFF;

checksum
--------
  281845
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'order_items'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Same checksum, but the RANGE version did 681 scans and 4087 logical reads on Worktable, the ROWS version zero. For a RANGE frame SQL Server uses a worktable (window spool) in tempdb so that it can see all rows sharing a value; ROWS UNBOUNDED PRECEDING is computed on the fast in-memory path. The difference is clear even at 680 rows; at millions of rows it decides whether a report takes seconds or minutes. Relying on the default frame risks both a wrong result and a slow query. (The Window Aggregate operator running in batch mode does not use this spool; when it kicks in belongs to the SQL Server Derinlemesine (SQL Server in Depth) series.)

Moving Averages and Missing Days

For a 7-day moving average the first thing that comes to mind is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW:

WITH daily AS (
    SELECT CAST(o.ordered_at AS date) AS d, COUNT(*) AS orders
    FROM dbo.orders AS o
    WHERE o.ordered_at >= '2025-12-01' AND o.ordered_at < '2026-01-01'
    GROUP BY CAST(o.ordered_at AS date)
),
averaged AS (
    SELECT d, orders,
           CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_rows
    FROM daily
)
SELECT d, orders, avg_7_rows
FROM averaged
WHERE d >= '2025-12-20'
ORDER BY d;

d          orders avg_7_rows
---------- ------ ----------
2025-12-21      2       1.43
2025-12-22      4       1.86
2025-12-23      1       1.71
2025-12-24      2       1.86
2025-12-25      1       1.86
2025-12-26      1       1.86
2025-12-27      1       1.71
2025-12-28      1       1.57
2025-12-29      1       1.14
2025-12-30      1       1.14
2025-12-31      2       1.14

I computed the window over the whole of December inside a CTE (Common Table Expression) and applied the filter outside (I will come back to why that matters shortly). The result looks reasonable but it is wrong. There were no orders on 14, 16 and 20 December, so daily has no rows for those days either. A ROWS frame counts rows, not days: the "last 7 rows" for 21 December actually reach back to 12 December. Every day without orders stretches the window one more day into the past. The fix is to produce a complete calendar first and LEFT JOIN to it:

WITH days AS (
    SELECT DATEADD(day, value, CAST('2025-12-01' AS date)) AS d
    FROM GENERATE_SERIES(0, 30)
),
daily AS (
    SELECT days.d, COUNT(o.order_id) AS orders
    FROM days
    LEFT JOIN dbo.orders AS o
           ON o.ordered_at >= days.d AND o.ordered_at < DATEADD(day, 1, days.d)
    GROUP BY days.d
),
averaged AS (
    SELECT d, orders,
           CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_days
    FROM daily
)
SELECT d, orders, avg_7_days
FROM averaged
WHERE d >= '2025-12-20'
ORDER BY d;

d          orders avg_7_days
---------- ------ ----------
2025-12-20      0 .71
2025-12-21      2 1.00
2025-12-22      4 1.43
2025-12-23      1 1.57
2025-12-24      2 1.71
2025-12-25      1 1.71
2025-12-26      1 1.57
2025-12-27      1 1.71
2025-12-28      1 1.57
2025-12-29      1 1.14
2025-12-30      1 1.14
2025-12-31      2 1.14

I produced the calendar with GENERATE_SERIES (SQL Server 2022+); the recursive CTE we saw in post 12 does the same job. Now 20 December is in the list with 0 orders, and the average for 21 December is 1.00 rather than 1.43. The difference looks small, but if a dashboard says "last 7 days", it should really be 7 days.

Now to why that CTE was needed. If you write the filter in the same query as the window function:

WITH daily AS (
    SELECT CAST(o.ordered_at AS date) AS d, COUNT(*) AS orders
    FROM dbo.orders AS o
    WHERE o.ordered_at >= '2025-12-01' AND o.ordered_at < '2026-01-01'
    GROUP BY CAST(o.ordered_at AS date)
)
SELECT d, orders,
       CAST(AVG(orders * 1.0) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS DECIMAL(5,2)) AS avg_7_rows
FROM daily
WHERE d >= '2025-12-20'
ORDER BY d;

d          orders avg_7_rows
---------- ------ ----------
2025-12-21      2       2.00
2025-12-22      4       3.00
2025-12-23      1       2.33
2025-12-24      2       2.25
2025-12-25      1       2.00
2025-12-26      1       1.83
2025-12-27      1       1.71
2025-12-28      1       1.57
2025-12-29      1       1.14
2025-12-30      1       1.14
2025-12-31      2       1.14

The average for 21 December came out as 2.00, because WHERE runs before the window and deletes every row before 20 December before the window is computed. The window only sees the rows that are left. The rule: if a window function needs to look into the past, compute it over the wider data and filter the displayed range in the outer query.

The standard solves this problem more elegantly: with RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW you define the frame by a value range, and missing days are no problem. PostgreSQL supports it (in my test RANGE BETWEEN INTERVAL '6 days' PRECEDING worked as expected). SQL Server, however, accepts only UNBOUNDED and CURRENT ROW with RANGE:

SELECT CAST(ordered_at AS date) AS d,
       COUNT(*) OVER (ORDER BY CAST(ordered_at AS date) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS last_7_days
FROM dbo.orders;

Msg 4194, Level 16, State 1, Server b8be943222fa, Line 1
RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.

Previous and Next Row: LAG, LEAD, FIRST_VALUE, LAST_VALUE

LAG fetches the value of the previous row, LEAD the next one; no self join needed. Monthly revenue and the change against the previous month:

WITH monthly AS (
    SELECT DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1) AS month_start,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM dbo.orders AS o
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled' AND o.ordered_at >= '2025-07-01' AND o.ordered_at < '2026-01-01'
    GROUP BY DATEFROMPARTS(YEAR(o.ordered_at), MONTH(o.ordered_at), 1)
)
SELECT month_start, revenue,
       LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
       CAST(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month_start))
            / LAG(revenue) OVER (ORDER BY month_start) AS DECIMAL(6,1)) AS change_pct,
       LEAD(month_start, 1, '9999-12-31') OVER (ORDER BY month_start) AS next_month
FROM monthly
ORDER BY month_start;

month_start  revenue prev_revenue change_pct next_month
----------- -------- ------------ ---------- ----------
2025-07-01  12866.70         NULL       NULL 2025-08-01
2025-08-01  12681.50     12866.70       -1.4 2025-09-01
2025-09-01  33349.23     12681.50      163.0 2025-10-01
2025-10-01  27489.90     33349.23      -17.6 2025-11-01
2025-11-01  20325.80     27489.90      -26.1 2025-12-01
2025-12-01  41866.20     20325.80      106.0 9999-12-31

The first month has no previous month, so LAG returned NULL and the change is NULL too. LEAD's third argument is a default value: instead of the month after the last one it wrote 9999-12-31. The second argument is how many rows to look back or ahead; LAG(revenue, 12) fetches the same month last year.

The same function works per customer with PARTITION BY. The number of days between a customer's consecutive orders, the basis of repurchase analysis:

SELECT TOP (6) order_id, ordered_at,
       DATEDIFF(day, LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id), ordered_at) AS days_since_prev
FROM dbo.orders
WHERE customer_id = 5
ORDER BY ordered_at, order_id;

order_id ordered_at          days_since_prev
-------- ------------------- ---------------
      56 2025-03-31 21:48:08            NULL
      57 2025-04-02 09:27:19               2
     107 2025-06-24 15:45:36              83
     208 2025-11-20 15:27:45             149
     359 2026-05-15 12:00:31             176

FIRST_VALUE and LAST_VALUE fetch the first and last row of the frame, and here the default frame from the opening shows up once more:

SELECT title, list_price,
       FIRST_VALUE(title) OVER (ORDER BY list_price DESC, book_id) AS most_expensive,
       LAST_VALUE(title)  OVER (ORDER BY list_price DESC, book_id) AS cheapest_wrong,
       LAST_VALUE(title)  OVER (ORDER BY list_price DESC, book_id
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cheapest
FROM dbo.books
WHERE category_id = 4
ORDER BY list_price DESC, book_id;

title                list_price most_expensive      cheapest_wrong       cheapest
-------------------- ---------- ------------------- -------------------- ---------------
The Handmaid's Tale      580.50 The Handmaid's Tale The Handmaid's Tale  Brave New World
Nineteen Eighty-Four     530.90 The Handmaid's Tale Nineteen Eighty-Four Brave New World
Fahrenheit 451           530.90 The Handmaid's Tale Fahrenheit 451       Brave New World
Brave New World          320.90 The Handmaid's Tale Brave New World      Brave New World

FIRST_VALUE wrote the most expensive book on every row, as expected. LAST_VALUE, however, wrote each row's own title: because the default frame is "from the start of the partition to the current row", the last row of the frame is always the current row. For the right result you have to open the frame to the end of the partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. In practice I prefer writing FIRST_VALUE with the reverse ordering instead of LAST_VALUE; it does not trip over the frame. Since SQL Server 2022, FIRST_VALUE and LAST_VALUE also support the standard's IGNORE NULLS option.

Median: PERCENTILE_CONT and PERCENTILE_DISC

The average order value is easily inflated by a few large orders; the median is a more honest measure. PERCENTILE_CONT interpolates linearly between ordered values, PERCENTILE_DISC returns a value that actually exists in the set:

WITH order_totals AS (
    SELECT o.order_id, c.country_code, SUM(oi.quantity * oi.unit_price) AS total
    FROM dbo.orders AS o
    JOIN dbo.customers AS c    ON c.customer_id = o.customer_id
    JOIN dbo.order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'Cancelled'
    GROUP BY o.order_id, c.country_code
)
SELECT DISTINCT country_code,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) OVER (PARTITION BY country_code) AS median_cont,
       PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total) OVER (PARTITION BY country_code) AS median_disc,
       AVG(total) OVER (PARTITION BY country_code) AS mean_total
FROM order_totals
ORDER BY country_code;

country_code        median_cont median_disc  mean_total
------------ ------------------ ----------- -----------
DE           940.89999999999998      940.90 1027.002298
GB                        660.0      660.00  916.208604
NL                       1490.7     1430.90 1233.150000
TR                       847.62      847.62 1031.972648

Three things to notice. NL (Netherlands) has an even number of orders, so the two methods disagree: PERCENTILE_CONT took the mean of the two middle values, PERCENTILE_DISC the first of them. The 940.89999999999998 for DE (Germany) is the result of PERCENTILE_CONT returning float; when reporting money, CAST it to decimal. Third, the DISTINCT: in SQL Server these two functions work only as window functions, so they write the result on every row and you need DISTINCT for one row per country. In the standard and in PostgreSQL they are ordered-set aggregates, used with GROUP BY and without OVER. Try the same in SQL Server:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY list_price) AS median_price
FROM dbo.books;

Msg 10753, Level 15, State 3, Server b8be943222fa, Line 1
The function 'PERCENTILE_CONT' must have an OVER clause.

Gaps and Islands

Questions like "the days a customer ordered in a row" or "periods with no orders at all" are known as the gaps and islands problem. Grace's (customer 3) order days at the end of November:

d
----------
2025-11-24
2025-11-25
2025-11-26
2025-11-27
2025-12-01
2025-12-02

Four days in a row from 24 to 27 November, then two days on 1–2 December. The classic trick for finding these islands: subtract from consecutive dates as many days as their sequence number, and all dates in the same island give the same value.

WITH days AS (
    SELECT DISTINCT CAST(ordered_at AS date) AS d
    FROM dbo.orders
    WHERE customer_id = 3 AND ordered_at >= '2025-11-20' AND ordered_at < '2025-12-05'
)
SELECT d,
       ROW_NUMBER() OVER (ORDER BY d) AS rn,
       DATEADD(day, -ROW_NUMBER() OVER (ORDER BY d), d) AS grp
FROM days
ORDER BY d;

d          rn grp
---------- -- ----------
2025-11-24  1 2025-11-23
2025-11-25  2 2025-11-23
2025-11-26  3 2025-11-23
2025-11-27  4 2025-11-23
2025-12-01  5 2025-11-26
2025-12-02  6 2025-11-26

24 November − 1 day = 23 November, 25 November − 2 days = 23 November... When the island breaks (the 28–30 November gap), the date moves faster than the number and grp changes. Now grouping by grp is enough. Streaks of at least three days for all customers:

WITH days AS (
    SELECT DISTINCT customer_id, CAST(ordered_at AS date) AS d
    FROM dbo.orders
),
grouped AS (
    SELECT customer_id, d,
           DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY d), d) AS grp
    FROM days
)
SELECT customer_id, MIN(d) AS streak_start, MAX(d) AS streak_end, COUNT(*) AS days_in_a_row
FROM grouped
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3
ORDER BY days_in_a_row DESC, customer_id, streak_start;

customer_id streak_start streak_end days_in_a_row
----------- ------------ ---------- -------------
          3 2025-11-24   2025-11-27             4

The only precondition is that dates are unique: if there are two orders on the same day, the sequence number moves faster than the date and the calculation breaks. That is why I used DISTINCT in the days CTE. Finding the gaps is easier with LEAD: look at each day's next order day, and if more than one day lies between them, there is a gap. The longest periods without any orders in Bookstore:

WITH days AS (
    SELECT DISTINCT CAST(ordered_at AS date) AS d FROM dbo.orders
),
next_day AS (
    SELECT d, LEAD(d) OVER (ORDER BY d) AS next_d FROM days
)
SELECT TOP (5) DATEADD(day, 1, d) AS gap_start, DATEADD(day, -1, next_d) AS gap_end,
       DATEDIFF(day, d, next_d) - 1 AS days_without_orders
FROM next_day
WHERE DATEDIFF(day, d, next_d) > 1
ORDER BY days_without_orders DESC, gap_start;

gap_start  gap_end    days_without_orders
---------- ---------- -------------------
2025-02-23 2025-03-03                   9
2026-05-16 2026-05-24                   9
2025-01-03 2025-01-09                   7
2026-05-07 2026-05-13                   7
2025-07-07 2025-07-12                   6

Production Notes

If you repeat the same window definition in many functions, the WINDOW clause that came with SQL Server 2022 lets you write the definition once. It is part of the standard and also exists in PostgreSQL and MySQL; in SQL Server it requires compatibility level 160 or higher:

SELECT title, list_price,
       MIN(list_price) OVER w AS min_price,
       MAX(list_price) OVER w AS max_price,
       AVG(list_price) OVER w AS avg_price
FROM dbo.books
WHERE category_id = 12
WINDOW w AS (PARTITION BY category_id);

title                           list_price min_price max_price   avg_price
------------------------------- ---------- --------- --------- -----------
The Art of Computer Programming    1450.00   1150.00   1450.00 1300.000000
The Algorithm Design Manual        1150.00   1150.00   1450.00 1300.000000

The cost of window functions comes from the sort done for partitioning and ordering, and from frame computation. An index that starts with the partition and ordering columns removes the sort; writing the frame as ROWS removes the worktable. You can measure the effect of both with logical reads; do not say "window functions are slow" without measuring.

In reports like moving averages and YTD, date gaps are the most common silent error. Always do the computation on data joined to a complete calendar; that is why a calendar table lives on as a permanent table in many teams.

The gaps and islands trick also works with a sequence of numbers instead of dates (invoice numbers, sensor reading sequence). The rule is the same: a run continues as long as "value − sequence number" stays constant.

Common Pitfalls

Writing SUM() OVER (ORDER BY ...) without specifying the frame. The default RANGE gives the same total to rows with equal values and brings the worktable cost.

Using LAST_VALUE with the default frame. Every row returns itself; open the frame to UNBOUNDED FOLLOWING or write FIRST_VALUE with the reverse ordering.

Computing "the last 7 days" with ROWS BETWEEN 6 PRECEDING. Missing days stretch the window; join to a calendar first.

Forgetting that PERCENTILE_CONT returns float. Convert money to decimal.

Not deduplicating repeated dates with DISTINCT in gaps and islands.

Deleting the past rows a window function needs to look at with a WHERE in the same query. Compute inside a CTE first, then filter.

Using the WINDOW clause in a database at an old compatibility level and saying "it worked on SQL Server 2022".

A Small Experiment

★☆☆ Compute a per-customer running spend (PARTITION BY customer_id) over each customer's orders. Write the frame explicitly as ROWS and add order_id to the ordering.

★★☆ Compute the change in monthly revenue against the same month last year with LAG(revenue, 12). The result should be filled for the first six months of 2026 and NULL for the months of 2025. Why?

★★★ For each book, find "the longest streak of consecutive days it sold": extract the islands from the days the book was ordered and return each book's longest island. Then write the same query for "the longest period without any orders" using LEAD.

Throughout the series we have only been reading with window functions. In the next post we change data: multi-row INSERT, INSERT ... SELECT, getting new ids with OUTPUT, UPDATE ... FROM, the difference between DELETE and TRUNCATE, the traps of MERGE, and how to protect yourself against an UPDATE without a WHERE.