Window Frames, Ranking, Running Totals, Gaps-and-Islands, and Top-N per Group

Learning outcomes

An aggregate report can tell you that a technician handled five orders, but it cannot show each order and its rank within that technician at the same time without rejoining or nesting. Window functions solve that class of problem: they calculate over a related set of rows while preserving each current row in the result.

The hard part is not memorizing ROW_NUMBER() or LAG(). The hard part is defining the partition, ordering, peer relationships, and frame precisely enough that ties and duplicate timestamps do not silently change the answer.

Partition, window order, output order, and frame are different controls

PARTITION BY divides rows into independent analytical groups. The ORDER BY inside OVER(...) defines sequence within each partition for functions that need ordering. A final query ORDER BY controls presentation of the returned rows. A frame, when relevant, selects a moving subset of the current partition for the current row.

USE servicehub_analytics_lab;SELECT technician_id,       work_order_id,       labor_minutes,       ROW_NUMBER() OVER (         PARTITION BY technician_id         ORDER BY labor_minutes DESC, work_order_id       ) AS row_noFROM work_ordersWHERE technician_id IS NOT NULLORDER BY technician_id, row_no;

The final ORDER BY technician_id, row_no is presentation. The window ORDER BY labor_minutes DESC, work_order_id defines how ROW_NUMBER() is assigned. Those clauses can be different because they answer different questions.

ROW_NUMBER, RANK, and DENSE_RANK: ties are business semantics

Technician 13 has two 60-minute work orders (1004 and 1008). That tie lets us observe the three ranking models. ROW_NUMBER() always assigns distinct sequence numbers. RANK() gives peers the same rank and leaves gaps after a tie. DENSE_RANK() gives peers the same rank without gaps.

SELECT technician_id,       work_order_id,       labor_minutes,       ROW_NUMBER() OVER (         PARTITION BY technician_id ORDER BY labor_minutes DESC, work_order_id       ) AS deterministic_row_no,       RANK() OVER (         PARTITION BY technician_id ORDER BY labor_minutes DESC       ) AS labor_rank,       DENSE_RANK() OVER (         PARTITION BY technician_id ORDER BY labor_minutes DESC       ) AS dense_labor_rankFROM work_ordersWHERE technician_id = 13ORDER BY labor_minutes DESC, work_order_id;

Do not add a unique tie breaker to the RANK() ordering if the business definition says equal labor should tie; doing so makes every row a different peer. Use the tie breaker for deterministic ROW_NUMBER() when exactly one row must be first.

The default frame can surprise you when ORDER BY has peers

For an aggregate used as a window function, adding window ORDER BY without an explicit frame gives MySQL a default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, all peer rows with the same ordering value are included at the same boundary. Work orders 1008 and 1009 share the same opened_at, so they are ideal probes.

SELECT work_order_id,       opened_at,       parts_cost,       SUM(parts_cost) OVER (         ORDER BY opened_at       ) AS default_running_cost,       SUM(parts_cost) OVER (         ORDER BY opened_at, work_order_id         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW       ) AS row_by_row_running_costFROM work_ordersORDER BY opened_at, work_order_id;

The explicit ROWS frame plus a deterministic ordering states a row-by-row running-total contract. The default RANGE groups peers at the same ordering value, so both March 3 rows can see the peer-inclusive cumulative value. Neither is universally “correct”; choose the frame that matches the business question.

LAG and LEAD: compare adjacent rows without a self join

LAG() reads a previous row in window order and LEAD() reads a following row. They are useful for deltas, state transitions, and interval analysis. They operate on the ordered partition rather than on a frame in the same way aggregate windows do.

SELECT customer_id,       work_order_id,       opened_at,       LAG(opened_at) OVER (         PARTITION BY customer_id         ORDER BY opened_at, work_order_id       ) AS previous_opened_at,       TIMESTAMPDIFF(         DAY,         LAG(opened_at) OVER (           PARTITION BY customer_id           ORDER BY opened_at, work_order_id         ),         opened_at       ) AS days_since_previousFROM work_ordersORDER BY customer_id, opened_at, work_order_id;

The first row in each customer partition has no previous row, so LAG() returns NULL unless a default is specified. Preserve that unknown boundary rather than replacing it with zero unless zero is truly meaningful.

Top-N per group: calculate first, filter outside

A window function is not available to the same query block’s WHERE clause. A common mistake is to try WHERE ROW_NUMBER() OVER (...) <= 2. The correct pattern calculates the window result in a common table expression (CTE) or derived table, then filters that result in an outer query.

WITH ranked AS (  SELECT technician_id,         work_order_id,         labor_minutes,         ROW_NUMBER() OVER (           PARTITION BY technician_id           ORDER BY labor_minutes DESC, work_order_id         ) AS rn  FROM work_orders  WHERE technician_id IS NOT NULL)SELECT technician_id, work_order_id, labor_minutesFROM rankedWHERE rn <= 2ORDER BY technician_id, rn;

If ties should produce more than two rows, use RANK() or DENSE_RANK() and document that “top two ranks” is different from “exactly two rows.”

Gaps and islands: turn consecutive dates into groups

A classic analytical pattern asks for consecutive runs of activity. The service_days table contains deliberate gaps. If you number each customer’s dates, subtracting that row number (in days) from the service date produces the same anchor date for dates that are consecutive. Grouping by that anchor yields an “island.”

WITH numbered AS (  SELECT customer_id,         service_date,         ROW_NUMBER() OVER (           PARTITION BY customer_id           ORDER BY service_date         ) AS rn  FROM service_days), islanded AS (  SELECT customer_id,         service_date,         DATE_SUB(service_date, INTERVAL rn DAY) AS island_key  FROM numbered)SELECT customer_id,       MIN(service_date) AS island_start,       MAX(service_date) AS island_end,       COUNT(*) AS day_countFROM islandedGROUP BY customer_id, island_keyORDER BY customer_id, island_start;

Customer 1 should produce a three-day island (January 10–12) and a two-day island (January 20–21). The technique depends on one row per customer/date; duplicate dates would need to be deduplicated first or modeled deliberately.

Observe plans, but do not read window semantics from plan text

Use EXPLAIN and EXPLAIN ANALYZE to see sorting, materialization, row estimates, and actual iterator behavior. Window-heavy queries can require sorting and temporary work. Exact plan text is optimizer evidence for the current schema/data/version, not a stable application API.

EXPLAIN FORMAT=TREEWITH ranked AS (  SELECT technician_id, work_order_id, labor_minutes,         ROW_NUMBER() OVER (           PARTITION BY technician_id           ORDER BY labor_minutes DESC, work_order_id         ) AS rn  FROM work_orders)SELECT * FROM ranked WHERE rn <= 2;EXPLAIN ANALYZEWITH ranked AS (  SELECT technician_id, work_order_id, labor_minutes,         ROW_NUMBER() OVER (           PARTITION BY technician_id           ORDER BY labor_minutes DESC, work_order_id         ) AS rn  FROM work_orders)SELECT * FROM ranked WHERE rn <= 2;

On this tiny seed, timing is not a benchmark. The goal is learning to correlate the query’s logical stages with observable physical work.

Hands-on lab and verification

1. Compare ROW_NUMBER, RANK, and DENSE_RANK for technician 13 and explain the two 60-minute rows.
2. Run the default-frame and explicit-ROWS running-cost query. Explain the March 3 peer behavior.
3. Build top two rows per technician with ROW_NUMBER; then change to top two ranks with DENSE_RANK.
4. Use LAG() to calculate days between each customer’s work orders.
5. Find service-day islands and verify customer 1 manually.
6. Inspect EXPLAIN ANALYZE but do not record its local timing as a universal performance claim.

Production judgment and next bridge

Window functions often replace complex self joins cleanly, but they can require sorting large partitions. Partition cardinality, ordering columns, filtering before the window stage, and the number of window definitions all affect work. Measure representative workloads and watch temporary-table/sort behavior rather than assuming an index will make every window query cheap.

Next: the chapter moves from structured columns to MySQL’s native JSON type, relational projection with JSON_TABLE(), and evidence-driven indexing of frequently queried JSON attributes.

Authoritative references

• MySQL 8.4 Reference Manual — Window Functions
• MySQL 8.4 Reference Manual — Window Function Concepts and Syntax
• MySQL 8.4 Reference Manual — Window Function Frame Specification
• MySQL 8.4 Reference Manual — Window Function Descriptions
• MySQL 8.4 Reference Manual — Window Function Optimization