Grokking the Data Engineering Interview

0% completed

Window Frames and Running Totals

On This Page

The Table

First and Last Values

ROWS and RANGE

Partial First Windows

Functions That Ignore the Frame

Practice: Weekly Kits per Kitchen

Using This in an Interview

Key Takeaways

Practice Questions

The query below is meant to place the most recent day's order count next to every day, so that each day can be compared with the latest one. It runs without an error:

SELECT day_no, kits,
       LAST_VALUE(kits) OVER (ORDER BY day_no) AS latest_kits
FROM daily_kits
ORDER BY day_no;

Output:

+--------+------+-------------+
| day_no | kits | latest_kits |
+--------+------+-------------+
|      1 |  120 |         120 |
|      2 |  135 |         135 |
|      3 |  128 |         128 |
|      4 |  142 |         142 |
|      5 |  150 |         150 |
|      8 |   98 |          98 |
|      9 |  131 |         131 |
|     10 |  140 |         140 |
|     11 |  126 |         126 |
|     12 |  155 |         155 |
+--------+------+-------------+

Every row shows its own count, not the 155 kits of day 12. The function was asked about fewer rows than intended. For each row, a window function reads only a selected part of its partition (the frame, introduced in Window Functions), and with ORDER BY and no frame clause that part ends at the current row.

This lesson covers choosing that part on purpose: the two frame units, moving averages and their incomplete first windows, FIRST_VALUE and LAST_VALUE, and the functions that ignore the frame.

The Table

A meal-kit company records how many kits one kitchen ships each day. Days are integer day numbers, and the kitchen was closed on days 6 and 7, so those two days have no row.

day_no	kits
1	120
2	135
3	128
4	142
5	150
8	98
9	131
10	140
11	126
12	155

Every query below was run on MySQL 8.4, and the output shown is the real output.

First and Last Values

FIRST_VALUE and LAST_VALUE return the value from the first and the last row of the current frame, not of the whole partition. The MySQL manual lists them, together with NTH_VALUE and the aggregate functions, as functions that operate on the rows in the current frame.

With ORDER BY and no frame clause, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. On day 4, that frame holds days 1 through 4, so LAST_VALUE returns 142, which is day 4's own count. FIRST_VALUE has no such problem, because a frame of this kind always starts at the first row of the partition. The fix for LAST_VALUE is a frame that runs to the end of the partition:

SELECT day_no, kits,
       FIRST_VALUE(kits) OVER (ORDER BY day_no) AS first_kits,
       LAST_VALUE(kits) OVER (
         ORDER BY day_no
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS latest_kits
FROM daily_kits
ORDER BY day_no;

Output:

+--------+------+------------+-------------+
| day_no | kits | first_kits | latest_kits |
+--------+------+------------+-------------+
|      1 |  120 |        120 |         155 |
|      2 |  135 |        120 |         155 |
|      3 |  128 |        120 |         155 |
|      4 |  142 |        120 |         155 |
|      5 |  150 |        120 |         155 |
|      8 |   98 |        120 |         155 |
|      9 |  131 |        120 |         155 |
|     10 |  140 |        120 |         155 |
|     11 |  126 |        120 |         155 |
|     12 |  155 |        120 |         155 |
+--------+------+------------+-------------+

Now every row carries 155 from day 12, and the first-day count of 120 was correct without any change. PostgreSQL has the same default, and its manual says the default frame "is likely to give unhelpful results for last_value."

LAST_VALUE stops at the current row

There are two correct ways to read the latest value, and each has a cost.

LAST_VALUE with a full frame. It keeps the ascending order that the rest of the query uses. The cost is that forgetting the frame clause gives wrong numbers with no error.
FIRST_VALUE with the order reversed. FIRST_VALUE(kits) OVER (ORDER BY day_no DESC) returns 155 with the default frame, because the frame now starts at day 12. The cost appears when the same query has a window ordered the other way: the MySQL 8.4 plan for such a query shows two sorts and a temporary table between them.

ROWS and RANGE

A ROWS frame is defined by row positions, so 6 PRECEDING means the six rows before the current row, however far back their days are. The MySQL manual puts it as "the bound is expr rows before the current row."

A RANGE frame is defined by the value of the ORDER BY column, so 6 PRECEDING means every row whose day_no is at least the current day_no minus 6. For RANGE, the manual says the bound is "the rows with values equal to the current row value minus expr."

When every day has a row, the two frames hold the same rows. A gap in the days makes them differ. The query below computes a 7-day moving average (the average over the current day and the 6 days before it) both ways, with the first day each frame reaches:

SELECT day_no, kits,
       MIN(day_no) OVER (ORDER BY day_no
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rows_from,
       ROUND(AVG(kits) OVER (ORDER BY day_no
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS rows_avg,
       MIN(day_no) OVER (ORDER BY day_no
         RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS range_from,
       ROUND(AVG(kits) OVER (ORDER BY day_no
         RANGE BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS range_avg
FROM daily_kits
ORDER BY day_no;

Output:

+--------+------+-----------+----------+------------+-----------+
| day_no | kits | rows_from | rows_avg | range_from | range_avg |
+--------+------+-----------+----------+------------+-----------+
|      1 |  120 |         1 |    120.0 |          1 |     120.0 |
|      2 |  135 |         1 |    127.5 |          1 |     127.5 |
|      3 |  128 |         1 |    127.7 |          1 |     127.7 |
|      4 |  142 |         1 |    131.3 |          1 |     131.3 |
|      5 |  150 |         1 |    135.0 |          1 |     135.0 |
|      8 |   98 |         1 |    128.8 |          2 |     130.6 |
|      9 |  131 |         1 |    129.1 |          3 |     129.8 |
|     10 |  140 |         2 |    132.0 |          4 |     132.2 |
|     11 |  126 |         3 |    130.7 |          5 |     129.0 |
|     12 |  155 |         4 |    134.6 |          8 |     130.0 |
+--------+------+-----------+----------+------------+-----------+

Look at day 12. The ROWS frame starts at day 4, because the seven rows ending at day 12 are days 4, 5, 8, 9, 10, 11, and 12. That frame spans 9 calendar days, and its average is 942 / 7 = 134.6. The RANGE frame keeps days 6 through 12, and since days 6 and 7 have no row, it averages 5 rows: 650 / 5 = 130.0.

ROWS counts rows, RANGE measures days

RANGE with an offset has a requirement. MySQL accepts it only when the window has one ORDER BY expression of a numeric or temporal type, so a second sort column fails:

SELECT day_no, kits,
       SUM(kits) OVER (ORDER BY day_no, kits
         RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_kits
FROM daily_kits;

Output:

ERROR 3587 (HY000) at line 1: Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type

PostgreSQL has the same rule: its manual says RANGE with an offset requires "exactly one column" in ORDER BY.

	ROWS	RANGE with an offset
Frame is defined by	Row positions	Values of the ORDER BY column
A missing day	The frame reaches further back in time	The frame keeps its width in days
Tied ORDER BY values	Each row is placed separately	Tied rows enter the frame together
ORDER BY allowed	Any columns	One numeric or temporal expression

Cost of ROWS: the width in time depends on the data. On day 8, the ROWS frame reaches back to day 1, so its "7-day" average covers 8 calendar days. ROWS is the better choice when the table has exactly one row per day, or when the question is about the last N events rather than the last N days.
Cost of RANGE: one ordering column, and ties share a frame. Rows with equal day_no values are peers, so each one is always inside the other's frame. RANGE is the better choice when the window is a span of time and days can be missing.

Partial First Windows

A moving average has a partial window wherever the frame cannot yet reach its full width, which happens at the start of every partition. On day 1, the "7-day average" of 120.0 is the count of a single day, and day 5's 135.0 covers five days. On a chart, these values sit next to full 7-day averages but describe fewer days, so the two kinds of value are not comparable.

The second decision is what a missing day means. If the kitchen was closed, days 6 and 7 shipped zero kits, and the average per calendar day is the frame's SUM divided by 7. AVG divides by the number of rows in the frame instead, which answers a different question: the average per open day. The query below computes both, and it returns the calendar average only once the window is full:

SELECT day_no, kits,
       COUNT(*) OVER w7 AS open_days,
       ROUND(AVG(kits) OVER w7, 1) AS avg_open_day,
       CASE WHEN day_no - FIRST_VALUE(day_no) OVER (ORDER BY day_no) >= 6
            THEN ROUND(SUM(kits) OVER w7 / 7, 1)
       END AS avg_calendar_day
FROM daily_kits
WINDOW w7 AS (ORDER BY day_no RANGE BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY day_no;

Output:

+--------+------+-----------+--------------+------------------+
| day_no | kits | open_days | avg_open_day | avg_calendar_day |
+--------+------+-----------+--------------+------------------+
|      1 |  120 |         1 |        120.0 |             NULL |
|      2 |  135 |         2 |        127.5 |             NULL |
|      3 |  128 |         3 |        127.7 |             NULL |
|      4 |  142 |         4 |        131.3 |             NULL |
|      5 |  150 |         5 |        135.0 |             NULL |
|      8 |   98 |         5 |        130.6 |             93.3 |
|      9 |  131 |         5 |        129.8 |             92.7 |
|     10 |  140 |         5 |        132.2 |             94.4 |
|     11 |  126 |         5 |        129.0 |             92.1 |
|     12 |  155 |         5 |        130.0 |             92.9 |
+--------+------+-----------+--------------+------------------+

The WINDOW clause defines the frame once as w7, and each OVER refers to it. The CASE measures the distance from the first day with FIRST_VALUE, which is safe with the default frame. Day 8 is the first full window, because it is at least 6 days after day 1. On day 12 the two averages differ widely: 130.0 kits per open day, but 650 / 7 = 92.9 kits per calendar day.

Return NULL for partial windows when the result feeds a chart or an alert, because a one-day "average" would be compared with seven-day ones. The cost is that the first six days have no value.
Return the partial value with its row count when every row needs a number, so a reader sees how many days are behind it.
Divide by 7 only when a missing day means zero. If days are missing because a data feed failed, zero is wrong, and AVG over the days present is the better estimate.

Functions That Ignore the Frame

Ranking and offset functions use the whole partition and ignore any frame clause. MySQL accepts a frame for ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, PERCENT_RANK, and CUME_DIST, but the manual says it "ignores it." LAG(x, n) returns the value of x from the row n rows before the current one. The query below gives three functions one window with a two-row frame:

SELECT day_no, kits,
       ROW_NUMBER() OVER w1 AS row_num,
       LAG(kits, 3) OVER w1 AS kits_3_rows_back,
       SUM(kits) OVER w1 AS two_row_sum
FROM daily_kits
WINDOW w1 AS (ORDER BY day_no ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
ORDER BY day_no
LIMIT 5;

Output:

+--------+------+---------+------------------+-------------+
| day_no | kits | row_num | kits_3_rows_back | two_row_sum |
+--------+------+---------+------------------+-------------+
|      1 |  120 |       1 |             NULL |         120 |
|      2 |  135 |       2 |             NULL |         255 |
|      3 |  128 |       3 |             NULL |         263 |
|      4 |  142 |       4 |              120 |         270 |
|      5 |  150 |       5 |              135 |         292 |
+--------+------+---------+------------------+-------------+

Only SUM follows the frame: day 4's two-row sum is 128 + 142 = 270. ROW_NUMBER still counts from the start of the partition. LAG(kits, 3) on day 4 returns day 1's 120, which lies outside the two-row frame. LIMIT runs after the windows, so it only shortens the output.

Practice: Weekly Kits per Kitchen

The table daily_kits now has a third column, kitchen, and holds several kitchens, each with its own closed days. For each kitchen and each day that has a row, use the window made of that day and the 6 calendar days before it. Return kitchen, day_no, week_kits (the total kits in the window), open_days (the rows in the window), and avg_per_day. The last column is week_kits divided by 7 and rounded to the nearest integer, so a closed day counts as zero. Return only full windows: days whose day_no is at least 6 more than the kitchen's first day_no. Sort the result by kitchen, then day_no.

MYSQL

. . . .

Using This in an Interview

Before writing a moving average, ask whether every day has a row and what a missing day means. Then state the frame aloud: "I order by day number and use RANGE BETWEEN 6 PRECEDING AND CURRENT ROW, so the window covers seven calendar days even when some days have no row." Two follow-up questions and their answers:

"How would you handle the first six days?" Return NULL until the window is full, or return the value with its day count. A chart or an alert needs NULL, while a report that must show every day can show the count.
"What if the ordering column were a DATE?" MySQL accepts a temporal interval as the offset, as in RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW. The single ORDER BY rule still applies, and PostgreSQL has the same rule.

Key Takeaways

FIRST_VALUE, LAST_VALUE, and the aggregate functions read the frame. With ORDER BY and no frame clause, the frame ends at the current row, so LAST_VALUE returns the current row's value.
ROWS counts row positions, while RANGE measures distance in the ORDER BY value. When days are missing, only RANGE keeps a 7-day window 7 days wide.
RANGE with an offset needs exactly one numeric or temporal ORDER BY expression, in MySQL and in PostgreSQL.
Every moving average has partial windows at the start of a partition, so return NULL there or show how many rows are behind each value.
AVG divides by the rows present, while SUM divided by 7 treats a missing day as zero; the right choice depends on what a missing day means.
ROW_NUMBER, RANK, LAG, and the other ranking and offset functions ignore any frame clause.

The calendar average in this lesson had to assume what a missing day means, because the table has no row for it. A different approach generates one row for every day first and joins the data to it, which makes each gap visible and makes ROWS and RANGE agree. The next lesson, CTEs and Recursive CTEs, builds that list of days with a recursive query.

Practice Questions

1. A window has ORDER BY day_no and no frame clause. On day 9 of the kitchen table, what do FIRST_VALUE(kits) and LAST_VALUE(kits) return?