Window Functions, Frames, Ranking, Running Totals, Gaps-and-Islands, and Sessionization

Learning outcomes

ServiceHub now has enough work-order events that supervisors want running totals, technician rankings, idle-gap detection, and “sessions” of related activity. A developer writes SUM(amount) OVER (ORDER BY event_time) and assumes it always adds one row at a time. Two events share the same timestamp, however, and the displayed running total jumps by two rows at once. Another developer uses ROW_NUMBER() ordered only by a nonunique score and assumes the same technician will always receive rank 1. The problem is not syntax; it is an incomplete model of partitions, ordering, frames, peers, and deterministic tie-breaking.

1. A window keeps detail rows while calculating over related rows

A window function computes a value across a set of rows related to the current row without collapsing those rows into one group. PARTITION BY splits the input into independent windows, ORDER BY establishes a logical sequence inside each partition, and a frame further limits the ordered rows visible to frame-aware functions. This is different from ordinary GROUP BY, which changes the output grain by producing one row per group.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab06') IS NULL EXEC(N'CREATE SCHEMA lab06 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab06.WorkEvent;GOCREATE TABLE lab06.WorkEvent(    event_id      bigint        NOT NULL CONSTRAINT PK_lab06_WorkEvent PRIMARY KEY,    technician_id int           NOT NULL,    event_time    datetime2(0)  NOT NULL,    event_type    varchar(20)   NOT NULL,    billable_min  int           NOT NULL CONSTRAINT CK_lab06_WorkEvent_Min CHECK (billable_min >= 0));INSERT lab06.WorkEvent(event_id, technician_id, event_time, event_type, billable_min)VALUES(1,101,'2026-08-20T08:00:00','arrive',0),(2,101,'2026-08-20T08:15:00','work',15),(3,101,'2026-08-20T08:15:00','work',20),(4,101,'2026-08-20T09:10:00','work',30),(5,101,'2026-08-20T12:30:00','work',25),(6,102,'2026-08-20T08:05:00','work',10),(7,102,'2026-08-20T08:40:00','work',35),(8,102,'2026-08-20T10:45:00','work',15);GO

The two technician-101 rows at 08:15 are deliberate peers: they have equal values for the window order key. They make frame and ranking behavior visible without relying on a large dataset.

2. Default frames can make peer rows advance together

For aggregate window functions that accept a frame, Microsoft documents that an ORDER BY without an explicit ROWS/RANGE frame uses RANGE UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal ordering values as peers at the current boundary. Therefore the first 08:15 row can see both 08:15 rows even though only one physical row appears before it in the final display. If the requirement is “one row at a time in a total order,” use an explicit ROWS frame and a unique tie-breaker.

USE ServiceHubLab;GOSELECT event_id, technician_id, event_time, billable_min,       SUM(billable_min) OVER         (PARTITION BY technician_id ORDER BY event_time) AS default_running,       SUM(billable_min) OVER         (PARTITION BY technician_id ORDER BY event_time, event_id          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_runningFROM lab06.WorkEventWHERE technician_id = 101ORDER BY event_time, event_id;GO

Expected behavior: both 08:15 peer rows show the same default_running value because the default RANGE frame reaches all peers at the current order value. The explicit ROWS expression progresses by exactly one ordered row because (event_time,event_id) is unique. This is not a performance trick; it is a correctness choice about the frame.

3. Ranking functions need a business tie policy

ROW_NUMBER assigns distinct sequence numbers, while RANK and DENSE_RANK preserve ties differently. If the window ORDER BY does not uniquely identify rows, the relative row-number assignment among peers is not guaranteed. A ranking dashboard must decide whether ties are meaningful and, if not, which stable column resolves them.

USE ServiceHubLab;GOWITH TechnicianTotals AS(  SELECT technician_id, SUM(billable_min) AS total_min  FROM lab06.WorkEvent  GROUP BY technician_id)SELECT technician_id, total_min,       ROW_NUMBER() OVER (ORDER BY total_min DESC, technician_id) AS deterministic_row_number,       RANK()       OVER (ORDER BY total_min DESC) AS business_rank,       DENSE_RANK() OVER (ORDER BY total_min DESC) AS dense_business_rankFROM TechnicianTotalsORDER BY deterministic_row_number;GO

The unique technician identifier makes ROW_NUMBER deterministic for a fixed dataset. The rank functions intentionally omit that tie-breaker because equal totals should share a business rank. Do not add a unique key mechanically to every ranking expression; first decide whether the business definition wants ties.

4. LAG/LEAD turn neighboring rows into boundaries

LAG reads a prior row from the logical window order without a self-join. That makes event-gap reasoning concise. A gaps-and-islands solution typically marks a boundary, then cumulatively sums boundary flags to create an island number. Sessionization is the same idea with a time-gap rule: when the gap from the previous event exceeds a threshold, start a new session.

USE ServiceHubLab;GOWITH Ordered AS(  SELECT event_id, technician_id, event_time, billable_min,         LAG(event_time) OVER           (PARTITION BY technician_id ORDER BY event_time, event_id) AS prev_time  FROM lab06.WorkEvent), Boundaries AS(  SELECT *,         CASE WHEN prev_time IS NULL                   OR DATEDIFF(minute, prev_time, event_time) > 90              THEN 1 ELSE 0 END AS starts_session  FROM Ordered), Numbered AS(  SELECT *,         SUM(starts_session) OVER           (PARTITION BY technician_id ORDER BY event_time, event_id            ROWS UNBOUNDED PRECEDING) AS session_no  FROM Boundaries)SELECT technician_id, session_no,       MIN(event_time) AS session_start,       MAX(event_time) AS session_end,       SUM(billable_min) AS billable_minFROM NumberedGROUP BY technician_id, session_noORDER BY technician_id, session_no;GO

For technician 101, the 12:30 event begins a new session because the gap after 09:10 exceeds 90 minutes. The threshold is a domain rule, not a universal tuning value. If the source can contain late-arriving events, identical timestamps, or changed timestamps, define how re-sessionization should behave rather than assuming append-only data.

5. Plans expose physical work, not a guaranteed operator recipe

Window queries often need ordering and partition state. Depending on indexes, estimates, parallelism, and expression shape, plans can contain Sort, Segment, Sequence Project, Window Aggregate, or spool-like operators. A Sort can request memory and spill if its grant is insufficient; an index whose key order matches the partition/order requirement can sometimes reduce sorting work. Chapter 10 and Chapter 12 investigate those mechanics in depth. Here the goal is to capture evidence without claiming a fixed plan.

USE ServiceHubLab;GOSET STATISTICS IO ON;SELECT technician_id, event_time, event_id,       SUM(billable_min) OVER         (PARTITION BY technician_id ORDER BY event_time, event_id          ROWS UNBOUNDED PRECEDING) AS running_minFROM lab06.WorkEventORDER BY technician_id, event_time, event_id;SET STATISTICS IO OFF;GO

Save the actual plan and your own logical reads. Do not invent a “window functions are one scan” rule: multiple windows with incompatible orders can require additional work, and small lab data can choose a different strategy than production data.

6. Production judgment and verification

Mandatory prerequisites are SQL Server 2025 CU7 build 17.0.4065.4, compatibility level 170, Developer or Express, and permission to create/read/drop disposable lab06 objects. No restart or paid edition is required. Monitor sort spills, memory grants, large partitions, and unexpectedly repeated sorts when the pattern reaches production scale. Rollback is simply dropping the lab table; production query changes should be compared with actual plans and representative data before release.

Authoritative references

• OVER clause — partition, order, frame, and default-frame semantics
• ROW_NUMBER — ranking and nondeterminism notes
• LAG — previous-row access for ordered analyses
• Execution plans — physical-plan evidence
• SQL Server 2025 build versions — current servicing baseline