Grokking the Data Engineering Interview

0% completed

Window Functions

On This Page

The Table

GROUP BY Collapses Rows

A Window Keeps Every Row

Partition, Order, and Frame

Why WHERE Cannot Filter a Window

Applying the Daily Cap

What a Window Costs

Practice: Find the Tap That Reaches the Cap

Using This in an Interview

Key Takeaways

Practice Questions

There are two ways to compute a total over a set of rows in SQL. You can collapse the rows into one row per group and report the group's figure (an aggregate query with GROUP BY), or you can keep every row and attach the group's figure to each one (a window function). Both use the same aggregate functions, like SUM and COUNT, but the shape of the result decides which questions the query can answer.

This lesson follows one table of transit card taps. A rider taps a card at a station gate, and the gate charges a fare. The transit agency caps spending at 600 cents per card per day, so once a card has paid 600 cents, later rides that day are free. Applying the cap needs every tap together with what the card had spent before it, which is the job of a window function.

The Table

Each row is one tap. Fares are stored in cents as integers, and tap_id increases in the order the gates recorded the taps.

tap_id	card_id	day_no	tap_time	station	fare_cents
1	C-101	1	07:52	Harbor	250
2	C-202	1	08:05	Elm Street	250
3	C-101	1	12:30	Museum	175
4	C-101	1	17:40	Central	250
5	C-202	1	18:10	Riverside	250
6	C-101	1	21:15	Harbor	175

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

GROUP BY Collapses Rows

An aggregate query answers questions about groups. Here is how much each card spent on day 1:

SELECT card_id, COUNT(*) AS taps, SUM(fare_cents) AS spent_cents
FROM taps
GROUP BY card_id
ORDER BY card_id;

Output:

+---------+------+-------------+
| card_id | taps | spent_cents |
+---------+------+-------------+
| C-101   |    4 |         850 |
| C-202   |    2 |         500 |
+---------+------+-------------+

Card C-101 spent 850 cents, which is 250 cents over the cap. The query cannot say which tap went over, though, because the four taps were merged into one row. Once GROUP BY runs, the individual taps are no longer part of the result.

A Window Keeps Every Row

A window function computes a value for each row from a set of related rows, called the window, and it returns one value per row instead of merging the rows. The MySQL manual describes these functions as performing "a calculation using rows related to that row", and it notes that most aggregate functions can also be used as window functions. The PostgreSQL manual makes the same point: the rows "retain their separate identities."

The OVER clause is what turns an aggregate into a window function. Here the same SUM is computed per card, while every tap stays in the result:

SELECT tap_id, card_id, fare_cents,
       SUM(fare_cents) OVER (PARTITION BY card_id) AS card_total
FROM taps
ORDER BY card_id, tap_id;

Output:

+--------+---------+------------+------------+
| tap_id | card_id | fare_cents | card_total |
+--------+---------+------------+------------+
|      1 | C-101   |        250 |        850 |
|      3 | C-101   |        175 |        850 |
|      4 | C-101   |        250 |        850 |
|      6 | C-101   |        175 |        850 |
|      2 | C-202   |        250 |        500 |
|      5 | C-202   |        250 |        500 |
+--------+---------+------------+------------+

Notice that six taps went in and six rows came out. Each tap now carries its card's total, so one row can compare a tap with its group: tap 1 is 250 of 850 cents, or about 29 percent of the card's spending.

GROUP BY merges rows, a window keeps them

Partition, Order, and Frame

A window has three parts, and each one answers a different question about which rows the function sees.

Partition. PARTITION BY card_id splits the rows into groups, and a window never reaches across a partition boundary. Without PARTITION BY, the whole result is one partition.
Order. ORDER BY inside OVER sets the sequence of rows within each partition. It is separate from the query's final ORDER BY, which only sorts the output.
Frame. The frame is the part of the partition that the function reads for the current row. MySQL supports two frame units: ROWS, which counts row positions, and RANGE, which selects rows by value.

A running total needs all three. For each tap, the function should add up the same card's taps from the first one through the current one:

SELECT tap_id, card_id, tap_time, fare_cents,
       SUM(fare_cents) OVER (
         PARTITION BY card_id
         ORDER BY tap_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM taps
ORDER BY card_id, tap_id;

Output:

+--------+---------+----------+------------+---------------+
| tap_id | card_id | tap_time | fare_cents | running_cents |
+--------+---------+----------+------------+---------------+
|      1 | C-101   | 07:52    |        250 |           250 |
|      3 | C-101   | 12:30    |        175 |           425 |
|      4 | C-101   | 17:40    |        250 |           675 |
|      6 | C-101   | 21:15    |        175 |           850 |
|      2 | C-202   | 08:05    |        250 |           250 |
|      5 | C-202   | 18:10    |        250 |           500 |
+--------+---------+----------+------------+---------------+

Follow card C-101 down the column. The frame grows by one row each time: 250, then 250 + 175 = 425, then 425 + 250 = 675, then 675 + 175 = 850. Card C-202 starts again at 250, because it is a different partition.

A running total reads a growing frame

The frame clause is written out on purpose. When a window has ORDER BY but no frame clause, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes "all peers of the current row", meaning rows with the same ORDER BY value. Two taps with the same tap_time, ordered by tap_time, would therefore share one running total. Ordering by a unique column like tap_id and writing ROWS removes that ambiguity.

Why WHERE Cannot Filter a Window

The natural way to find the tap that crossed the cap is to put the window in WHERE. MySQL rejects it:

SELECT tap_id, card_id
FROM taps
WHERE SUM(fare_cents) OVER (PARTITION BY card_id ORDER BY tap_id) >= 600;

Output:

ERROR 3593 (HY000) at line 1: You cannot use the window function 'sum' in this context.'

The reason is evaluation order. The MySQL and PostgreSQL manuals both allow window functions only in the select list and ORDER BY, because windows run after WHERE, GROUP BY, and HAVING have decided which rows exist. When WHERE runs, the running total does not exist yet.

The fix is to compute the window in one query and filter it in an outer query. A named subquery written in a WITH clause (a common table expression, or CTE) keeps the two steps readable:

WITH running AS (
  SELECT tap_id, card_id, fare_cents,
         SUM(fare_cents) OVER (
           PARTITION BY card_id
           ORDER BY tap_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS running_cents
  FROM taps
)
SELECT tap_id, card_id, running_cents
FROM running
WHERE running_cents >= 600
  AND running_cents - fare_cents < 600
ORDER BY card_id, tap_id;

Output:

+--------+---------+---------------+
| tap_id | card_id | running_cents |
+--------+---------+---------------+
|      4 | C-101   |           675 |
+--------+---------+---------------+

The two conditions select the tap that crossed the cap. Tap 4 brought the total to 675, and the total before it was 675 - 250 = 425, below 600. Tap 6 is also over 600, but the card had reached the cap before it, so the second condition removes it.

Snowflake and BigQuery document a shortcut, a QUALIFY clause that "filters the results of window functions" without an outer query. MySQL 8.4 rejects QUALIFY with error 6037 unless an optional optimizer is enabled, and the PostgreSQL manual recommends a sub-select. All four engines document the WITH clause, so a CTE is the safer form when the dialect is uncertain.

Applying the Daily Cap

The same window can compute what each tap should charge. The card has already spent running_cents - fare_cents before the current tap, so the most the tap can charge is 600 minus that amount, and never less than zero:

WITH running AS (
  SELECT tap_id, card_id, fare_cents,
         SUM(fare_cents) OVER (
           PARTITION BY card_id
           ORDER BY tap_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) - fare_cents AS spent_before
  FROM taps
)
SELECT tap_id, card_id, fare_cents, spent_before,
       LEAST(fare_cents, GREATEST(600 - spent_before, 0)) AS charged_cents
FROM running
ORDER BY card_id, tap_id;

Output:

+--------+---------+------------+--------------+---------------+
| tap_id | card_id | fare_cents | spent_before | charged_cents |
+--------+---------+------------+--------------+---------------+
|      1 | C-101   |        250 |            0 |           250 |
|      3 | C-101   |        175 |          250 |           175 |
|      4 | C-101   |        250 |          425 |           175 |
|      6 | C-101   |        175 |          675 |             0 |
|      2 | C-202   |        250 |            0 |           250 |
|      5 | C-202   |        250 |          250 |           250 |
+--------+---------+------------+--------------+---------------+

Tap 4 charges 175 cents instead of 250, because only 175 cents remained under the cap, and tap 6 is free. Card C-101 pays 250 + 175 + 175 + 0 = 600 cents, while card C-202 never reaches the cap and pays its full 500.

What a Window Costs

A window with ORDER BY needs its rows in partition order before it can compute anything. The plan MySQL 8.4 prints for the running total shows this directly:

EXPLAIN FORMAT=TREE
SELECT tap_id, card_id,
       SUM(fare_cents) OVER (
         PARTITION BY card_id
         ORDER BY tap_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM taps;

Output:

-> Window aggregate: sum(taps.fare_cents) OVER (PARTITION BY taps.card_id ORDER BY taps.tap_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)   (cost=0 rows=6)
    -> Sort: taps.card_id, taps.tap_id  (cost=0.85 rows=6)
        -> Table scan on taps  (cost=0.85 rows=6)

Read the plan from the bottom up: MySQL scans the table, sorts it by card_id and tap_id, and then computes the window. Sorting six rows takes no measurable time. A city that records 2 million taps a day collects about 730 million in a year, and the sort has to order all of them.

The two query shapes compare as follows.

	GROUP BY	Window function
Rows returned	One per group	One per input row
Individual rows	Merged	Kept
Filtering on the result	HAVING	An outer query (CTE or derived table)
Typical question	What did each card spend?	Which tap crossed the cap?

Benefit of a window: detail and group figures in one row. Without a window, the same running total needs a self-join that matches each tap with every earlier tap of the same card. A card with 10 taps produces 55 matched pairs, and one with 100 taps produces 5,050.
Cost of a window: the result is as large as the input. Six taps give six rows, and a billion taps give a billion rows.
Cost of a window: a sort per distinct window. When one query has two windows, one partitioned by card and one by station, the MySQL 8.4 plan shows two sorts and a temporary table between them.

GROUP BY is the better choice when the question is only about groups, like a daily spending report per card, because it returns far fewer rows.

Practice: Find the Tap That Reaches the Cap

The table below now holds several days. For each card and day, return the tap at which the card's spending for that day first reached 600 cents or more. Return card_id, day_no, tap_id, and running_cents, which is the card's spending that day up to and including that tap. Order taps within a card and day by tap_id, and sort the result by card_id, then day_no. A card that never reaches 600 cents on a day does not appear for that day.

MYSQL

. . . .

Using This in an Interview

Meta's published preparation guide for its Data Engineer, Product Analytics role describes a 25-minute SQL part on PostgreSQL and says standard ANSI SQL is enough. Several companies say any standard SQL is fine, but ask the recruiter which dialect the screen uses, and default to a window in a CTE filtered by an outer query.

While writing, name the three parts out loud: "I partition by card and day, order by tap ID, and use a ROWS frame from the start of the partition to the current row." Two follow-up questions are worth preparing for:

"What happens if two taps have the same timestamp?" With the default RANGE frame they become peers and share one running total. Order by a unique column, or add one as a tiebreaker, and write ROWS.
"How would you write this in Snowflake?" The same CTE works, and QUALIFY can replace the outer query.

Key Takeaways

An aggregate with GROUP BY returns one row per group, while a window function returns one value for every input row.
OVER defines the window: PARTITION BY sets the groups, ORDER BY sets the sequence, and the frame sets which rows the function reads.
With ORDER BY and no frame clause, MySQL uses a RANGE frame that includes peer rows, so order by a unique column and write ROWS when a running total must advance one row at a time.
Window functions run after WHERE, GROUP BY, and HAVING, so a window result is filtered in an outer query, usually a CTE.
A window needs its rows sorted by its partition and order columns, and its result is as large as its input.

A running total is one kind of window function. Another kind gives each row a position within its partition, which answers questions like "the three most played episodes of each show." The next lesson, Ranking and Ties, compares the three ranking functions and shows what each one does when two rows are tied.

Practice Questions

1. A query must return each tap together with the card's average fare for that day. Should it use GROUP BY or a window function, and why?