How is SQL tested in ML-engineer interviews?

Before you start

The coding interview patterns and method guide gives the wider context for this topic. This page uses plain table queries only.

Why interviewers ask this

Window functions separate candidates who can describe a result from candidates who can build it. Candidates report rounds that include window functions (lag, running total, moving average) and self-joins, a multi-step query that combines a window function, a self-join and a filtered aggregate, and two phrasings of "highest earnings per delivery executive per date"; no public source confirms the exact questions. A self-join is a join of a table to itself under two aliases, so each row can be matched with other rows of the same table. The same top-per-group shape appears in "most popular artists by country".

A weak answer reaches for GROUP BY and a self-join and loses the row you needed. A strong answer ranks inside a partition, filters the rank in an outer query, and says what happens to ties. A Staff-level answer also states which tie rule the business wants before typing the query.

Window functions from first principles

Window function

A window function is a SQL function that computes a value for each row from a set of rows related to that row, written with an OVER clause, and that keeps every input row instead of collapsing them into one row per group.

Partition

A partition is the group of rows that share the same values in the PARTITION BY columns of a window, so the function restarts its counting and its comparisons inside each group.

In plain words

Picture a results sheet for several races. A GROUP BY summary is a leaderboard with one line per race. A window function leaves every runner's line and adds one extra column, such as "place in this race". The analogy breaks in one place. A sheet is already sorted, but a window sorts only inside the function, using the ORDER BY you put in OVER.

In one precise sentence, a window function adds a computed column to every row, using only the rows of its partition and frame. The PostgreSQL documentation describes window functions as calculations across sets of rows related to the current query row. A built-in one such as rank needs an OVER clause, and an ordinary aggregate acts as a window function only when undefined follows it; otherwise it collapses the rows. The same syntax is documented for SQLite and MySQL 8.0, so the ideas below carry across those databases.

Step 1: What does the OVER clause define?

The OVER clause defines the window: the rows, and their order, that a window function may see. PARTITION BY splits the table into partitions; ORDER BY inside OVER orders the rows within each partition. The PostgreSQL reference states that the built-in functions depend on the ordering of the window definition, so always write the ordering: without it the answer is left to chance.

Input: the table rows. Output: the same rows, grouped and ordered in the function's view only. For "highest earnings per delivery executive per date", the partition columns are de_id and the date, and the ordering is earnings from high to low. You now know the shape of every window query: a function name, then OVER (PARTITION BY ... ORDER BY ...). Nothing has been filtered yet.

Step 2: How do ranking functions treat ties?

row_number gives every row in the partition its own number from 1, even when earnings tie. rank gives tied rows the same rank and then leaves gaps; the PostgreSQL reference defines it as the row_number of the first row of the peer group. Peers are rows that tie under the window's ORDER BY. dense_rank also shares a rank among peers but leaves no gaps, because it counts peer groups.

Take one delivery executive on one date with four orders earning 50, 50, 30 and 20 (illustrative). row_number gives 1, 2, 3, 4; rank gives 1, 1, 3, 4; dense_rank gives 1, 1, 2, 3.¹ Keeping rank 1 with rank returns both 50 orders; with row_number it returns one of them, chosen arbitrarily unless you add a tiebreaker column. That is the common error: using row_number when the question allows ties.

Step 3: How do you filter on a rank?

A window function result cannot be filtered in the same query's WHERE clause, so the standard pattern wraps the ranking in a subquery and filters outside it. A subquery is a SELECT statement written inside another query, in parentheses, whose result the outer query treats as a table. A common table expression is a named subquery introduced with WITH; both work. Some databases shorten this: Snowflake documents a undefined clause that filters on window results without a subquery, and BigQuery and DuckDB offer the same clause, while PostgreSQL and MySQL do not.

Input: rows plus a rank column. Output: only the rows whose rank passes the filter. This is the answer to the two "maximum earnings per delivery executive per date" questions and to "most popular artists by country" (rank artists inside each country by their play count, then keep rank 1). You now know the complete top-N-per-group pattern. Changing "top one" to "top few" is a change of the filter, not of the structure. The k closest points page shows the same top-k idea in code.

Step 4: How do lag and frames give comparisons and running totals?

lag returns the value from an earlier row in the partition, and a frame limits the rows an aggregate reads. Per the PostgreSQL reference, lag(value, offset, default) uses an offset of 1 and a default of NULL when you omit them, so the first row returns NULL instead of an error. A running total is a sum over a frame that starts at the partition start; a moving average is an avg over a short frame.

The PostgreSQL syntax section says the default frame runs from the start of the partition through the last peer of the current row, which is why last_value often misleads. With the default frame, rows that tie on the ORDER BY value get the same running total. State the frame explicitly with ROWS to get one row at a time:

SELECT day, amount,
       lag(amount) OVER (ORDER BY day) AS prev_amount,
       sum(amount) OVER (ORDER BY day
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       avg(amount) OVER (ORDER BY day
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM daily_sales;

The moving average reads the current row and the two before it. On daily amounts 10, 20, 30, 40, 50 (illustrative) the query returns the values below.¹ The prefix-sum page shows the running total in Python.

Why do ML-engineer rounds use window functions?

ML-engineer rounds use window functions because building training data means computing per-user features and labels from event tables without collapsing the rows. lag gives "the previous event's value" as a feature, a frame gives a rolling average, and lead gives "the next event" as a label. The frame decides whether the feature is honest:

SELECT user_id, event_time, amount,
       avg(amount) OVER (PARTITION BY user_id ORDER BY event_time
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_prev_7,
       lead(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event_time
FROM events;

A frame ending at 1 PRECEDING, as in the PostgreSQL frame syntax, stops one row before the current row, so the current row and every later row stay out of the feature. The label can then not leak into the feature, which is called point-in-time correctness. The frame counts rows (the 7 is illustrative), so say aloud whether you mean rows or days.

Worked example

Two delivery executives on one date. Executive 1 has orders A, B, C, D earning 50, 50, 30 and 20; executive 2 has orders E and F earning 40 and 25 (all illustrative). The task: the orders with maximum earnings per executive per date.

SELECT order_number, de_id, order_date, earnings
FROM (
  SELECT order_number, de_id, order_date, earnings,
         rank() OVER (PARTITION BY de_id, order_date
                      ORDER BY earnings DESC) AS rk
  FROM orders
) AS ranked
WHERE rk = 1;

The inner query adds rk; the outer query keeps rank 1. The result is orders A and B for executive 1 and order E for executive 2, three rows in total. With row_number instead, executive 1 would return only one of A and B.¹

Now the Nth-highest question. Salaries 90, 80, 80, 70, 60, 60, 50 (illustrative) have a fourth-highest distinct salary of 60. Candidates report this question with LIMIT and TOP forbidden. Forbidding them tests whether you can express rank logic; dense_rank or a correlated COUNT(DISTINCT ...) subquery both answer it. The dense_rank version:

SELECT DISTINCT salary
FROM (
  SELECT salary,
         dense_rank() OVER (ORDER BY salary DESC) AS dr
  FROM salaries
) AS ranked
WHERE dr = 4;

It returns 60. Using rank would return 70, because the tied 80s push 70 to rank 4.¹

The last row follows the documented formula (rank minus 1) divided by (partition rows minus 1), which is (3 − 1) / (5 − 1).¹

Trade-offs and edge cases

Choose the ranking function from the business rule, not from habit. The table below compares the three.

Edge cases to name aloud:

• Ties with row_number. The pick among tied rows is arbitrary unless the ORDER BY has a tiebreaker, so add a unique column when the output must be repeatable.
• last_value. With the default frame it returns the current peer group's last row, not the partition's. Give the frame an explicit end.
• Self-join versus window. A self-join compares rows by matching them to each other, which can multiply rows and cost more; a window function reads one ordered pass per partition. A self-join is still natural for pairing rows that need a custom match condition (a performance claim for your own database should come from its plan output).
• Syntax differs by database. Filtering on a window result without a subquery works with QUALIFY in some databases and needs the subquery pattern in others, so say which database you assume.

How to answer it in an interview

The spoken structure: ask about ties, name the partition and ordering, say why the filter lives in an outer query, then give the tie variant.

Common mistakes

• "I will use row_number and keep 1." (wrong) That drops tied winners at random. (right) "I use rank when ties should all stay, and row_number with a tiebreaker when exactly one row is wanted."
• "The fourth highest is where rank equals 4." (wrong) Tied values leave gaps. (right) "I use dense_rank, which counts distinct values."
• "I will filter rk = 1 in the WHERE of the same query." (wrong) The window column does not exist there yet. (right) "I rank in a subquery or common table expression (CTE) and filter outside."
• "last_value gives the end of the group." (wrong) The default frame stops at the current peer group. (right) "I set the frame to end at the partition's last row."
• "A window function is another GROUP BY." (wrong) It keeps every row. (right) "An aggregate with OVER adds a column; GROUP BY collapses rows."

WITH day_totals AS (            -- step 1: filtered aggregate
  SELECT customer_id, order_date, sum(amount) AS day_total
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id, order_date
),
ranked AS (                     -- step 2: window column
  SELECT customer_id, order_date, day_total,
         rank() OVER (PARTITION BY order_date
                      ORDER BY day_total DESC) AS rk
  FROM day_totals
)
SELECT t.order_date, t.customer_id, t.day_total,   -- step 3: self-join
       p.day_total AS previous_day_total
FROM ranked AS t
LEFT JOIN day_totals AS p
  ON p.customer_id = t.customer_id
 AND p.order_date = t.order_date - 1
WHERE t.rk = 1;

SELECT b.title
FROM books AS b
JOIN shelves AS s ON s.shelf_id = b.shelf_id
WHERE s.topic = 'databases';