SQL window function interview questions, with worked answers

Window functions are the part of a SQL round where data engineering interviews separate people who memorised syntax from people who can predict what a query returns. The questions repeat: top N per group, second highest value, latest row per key, running totals, change versus the previous day, streaks. The traps repeat too, and most of them come from ties, missing dates and the default window frame.

This guide works through those questions on one small table so you can check every result by eye. The queries were run in DuckDB, and the output under each query is what it printed. The syntax is standard SQL and works the same way in PostgreSQL, Snowflake, BigQuery and Spark SQL unless a note says otherwise.

The table used in every example

8 orders from two cities. Two details are on purpose: Pune and Chennai each have 2 orders with the same amount, and Asha has 2 orders on the same date. Most interview mistakes with window functions show up only when data has ties like these.

SELECT * FROM orders ORDER BY order_id;

order_id | customer | city    | order_date | amount
---------+----------+---------+------------+-------
101      | Asha     | Pune    | 2026-03-01 | 1200
102      | Ravi     | Pune    | 2026-03-01 | 800
103      | Asha     | Pune    | 2026-03-02 | 1200
104      | Asha     | Pune    | 2026-03-02 | 300
105      | Meena    | Chennai | 2026-03-02 | 2500
106      | Kiran    | Chennai | 2026-03-03 | 2500
107      | Ravi     | Pune    | 2026-03-03 | 450
108      | Meena    | Chennai | 2026-03-05 | 900
(8 rows)

A window function computes a value for each row from a group of related rows, without collapsing them the way GROUP BY does. PARTITION BY decides which rows belong together, ORDER BY inside OVER (...) decides their order, and the frame decides which of those rows the calculation can see. If those 3 words are new, the SQL track builds them up from scratch before you come back here.

1What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows inside a partition. They only disagree when 2 rows tie on the ORDER BY value, which is exactly what interviewers test. Before you look at the output, predict what each function gives the two 2500 orders in Chennai.

SELECT city, order_id, amount,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS row_num,
       RANK()       OVER (PARTITION BY city ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS dense_rnk
FROM orders
ORDER BY city, amount DESC, order_id;

city    | order_id | amount | row_num | rnk | dense_rnk
--------+----------+--------+---------+-----+----------
Chennai | 105      | 2500   | 1       | 1   | 1
Chennai | 106      | 2500   | 2       | 1   | 1
Chennai | 108      | 900    | 3       | 3   | 2
Pune    | 101      | 1200   | 1       | 1   | 1
Pune    | 103      | 1200   | 2       | 1   | 1
Pune    | 102      | 800    | 3       | 3   | 2
Pune    | 107      | 450    | 4       | 4   | 3
Pune    | 104      | 300    | 5       | 5   | 4
(8 rows)

One line in that table matters more than it looks. ROW_NUMBER gave order 105 the number 1 and order 106 the number 2, but nothing in the query asked for that. The 2 rows tie on amount, so the database is free to number them either way, and a rerun on a different engine or a larger table can flip them. If the order of tied rows matters, add a tie-breaker such as order_id to the ORDER BY.

Practise it: RANK vs DENSE_RANK vs ROW_NUMBER and Top three salaries with ties.

2Why can't you filter on a window function in WHERE?

The most common first attempt at "top 2 orders per city" filters the row number directly:

SELECT order_id, ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM orders
WHERE rn <= 2;

Binder Error: WHERE clause cannot contain window functions!

The reason is the order in which SQL evaluates a query. WHERE runs before the SELECT list is computed, so when WHERE filters rows the window function has not produced a value yet. The error text depends on the engine. DuckDB resolves the alias and reports the window function; PostgreSQL says the column rn does not exist. The cause is the same.

The fix is to compute the window in one step and filter in the next, usually with a CTE:

WITH ranked AS (
  SELECT city, order_id, customer, amount,
         ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC, order_id) AS rn
  FROM orders
)
SELECT city, order_id, customer, amount
FROM ranked
WHERE rn <= 2
ORDER BY city, rn;

city    | order_id | customer | amount
--------+----------+----------+-------
Chennai | 105      | Meena    | 2500
Chennai | 106      | Kiran    | 2500
Pune    | 101      | Asha     | 1200
Pune    | 103      | Asha     | 1200
(4 rows)

Snowflake, BigQuery, Databricks SQL and DuckDB also support QUALIFY, which filters on a window function without a CTE. PostgreSQL and MySQL do not, so in an interview write the CTE first and mention QUALIFY as the shorter form where it exists. Practise it: Top three products per category.

3Top N per group: what should happen to ties?

The query above returned exactly 2 rows per city, and in both cities the 2 rows are the tied pair. Whether that is right depends on a question you should ask the interviewer: "if 2 orders tie for second place, do you want both?"

If the answer is "top two distinct amounts, all orders that have them", switch to DENSE_RANK:

WITH ranked AS (
  SELECT city, order_id, customer, amount,
         DENSE_RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS dr
  FROM orders
)
SELECT city, order_id, customer, amount, dr
FROM ranked
WHERE dr <= 2
ORDER BY city, dr, order_id;

city    | order_id | customer | amount | dr
--------+----------+----------+--------+---
Chennai | 105      | Meena    | 2500   | 1
Chennai | 106      | Kiran    | 2500   | 1
Chennai | 108      | Meena    | 900    | 2
Pune    | 101      | Asha     | 1200   | 1
Pune    | 103      | Asha     | 1200   | 1
Pune    | 102      | Ravi     | 800    | 2
(6 rows)

Same question, 6 rows instead of four. Neither answer is wrong. Saying out loud which one you chose, and why, is what the interviewer is listening for.

4How do you find the second highest value?

Across all 8 orders the distinct amounts, highest first, are 2500, 1200, 900, 800, 450 and 300. The second highest is 1200.

WITH ranked AS (
  SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS dr
  FROM orders
)
SELECT DISTINCT amount AS second_highest
FROM ranked
WHERE dr = 2;

second_highest
--------------
1200
(1 row)

Now swap DENSE_RANK for RANK and keep = 2. Predict the result before reading on.

WITH ranked AS (
  SELECT amount, RANK() OVER (ORDER BY amount DESC) AS r
  FROM orders
)
SELECT DISTINCT amount FROM ranked WHERE r = 2;

amount
------
(0 rows)

No rows at all. The two 2500 orders share rank 1, so RANK jumps straight to 3 and no row ever has rank 2. This is the classic bug in "second highest salary" answers, and it only appears when the top value is duplicated, which is why interviewers put a duplicate there. The DISTINCT is needed because 2 orders share 1200. Practise it: Second highest salary and Second highest salary per department.

5How do you keep only the latest row per key?

This is the deduplication pattern data engineers use most often: a source sends several versions of a record and you keep the newest. Here a customer profile table records each city change.

WITH ranked AS (
  SELECT customer, city, updated_at,
         ROW_NUMBER() OVER (PARTITION BY customer ORDER BY updated_at DESC) AS rn
  FROM profile_updates
)
SELECT customer, city, updated_at
FROM ranked
WHERE rn = 1
ORDER BY customer;

customer | city       | updated_at
---------+------------+-----------------
Asha     | Mumbai     | 2026-03-04 18:30
Meena    | Coimbatore | 2026-03-03 12:00
Ravi     | Pune       | 2026-03-02 10:00
(3 rows)

It looks correct, but look at Meena. She has two updates at exactly 12:00 on 3 March, one to Coimbatore and one to Madurai. The query kept Coimbatore because the engine happened to number that row first, not because anything in the data says it is newer. Run it again tomorrow on a bigger table and you may get Madurai, and a downstream report will change with no code change.

Before trusting a "latest row" query, check whether the ordering column has ties for the same key:

SELECT customer, updated_at, COUNT(*) AS rows_at_this_time
FROM profile_updates
GROUP BY customer, updated_at
HAVING COUNT(*) > 1;

customer | updated_at       | rows_at_this_time
---------+------------------+------------------
Meena    | 2026-03-03 12:00 | 2
(1 row)

Then add a tie-breaker that is stable, such as an ingestion sequence number or a source offset if you have one. With nothing better, sort on another column so at least the choice is repeatable. In an interview, saying "I would ask what should win when the timestamps tie" is worth more than the query itself. Practise it: Deduplicate events with ROW_NUMBER (free), and read what QUALIFY is and how it simplifies latest-row queries.

6Running totals, and the frame most people never set

A running total adds up values from the start of the partition to the current row. Here is Asha's, written the way most people write it:

SELECT order_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer ORDER BY order_date) AS running_default
FROM orders
WHERE customer = 'Asha'
ORDER BY order_date, order_id;

order_id | order_date | amount | running_default
---------+------------+--------+----------------
101      | 2026-03-01 | 1200   | 1200
103      | 2026-03-02 | 1200   | 2700
104      | 2026-03-02 | 300    | 2700
(3 rows)

The second row should read 2400 (1200 + 1200), but it reads 2700. When a window has ORDER BY and no frame, the SQL standard default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats every row with the same order_date as one peer group, so both 2 March orders see each other and both get the total up to the end of that day.

Asking for a row-by-row frame and a tie-breaker gives a true running total:

SELECT order_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM orders
WHERE customer = 'Asha'
ORDER BY order_date, order_id;

order_id | order_date | amount | running_rows
---------+------------+--------+-------------
101      | 2026-03-01 | 1200   | 1200
103      | 2026-03-02 | 1200   | 2400
104      | 2026-03-02 | 300    | 2700
(3 rows)

If the question is "cumulative revenue at the end of each day", the first version is the one you want. If it is "running total after each order", it is the second. Practise it: Running total by customer and Yearly resetting running total.

7LAG for day-over-day change, and the day that is missing

LAG(x) returns x from the previous row in the window's order. A daily revenue change looks like this:

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, revenue,
       LAG(revenue) OVER (ORDER BY order_date) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY order_date) AS change
FROM daily
ORDER BY order_date;

order_date | revenue | prev_revenue | change
-----------+---------+--------------+-------
2026-03-01 | 2000    | NULL         | NULL
2026-03-02 | 4000    | 2000         | 2000
2026-03-03 | 2950    | 4000         | -1050
2026-03-05 | 900     | 2950         | -2050
(4 rows)

There were no orders on 4 March, so that day has no row. LAG means "previous row", not "previous calendar day", which makes the 5 March line compare against 3 March. If the business wants day-over-day change, build the calendar first and join the facts to it:

WITH days AS (
  SELECT CAST(d AS DATE) AS order_date
  FROM generate_series(DATE '2026-03-01', DATE '2026-03-05', INTERVAL 1 DAY) AS t(d)
),
daily AS (
  SELECT d.order_date, COALESCE(SUM(o.amount), 0) AS revenue
  FROM days d
  LEFT JOIN orders o ON o.order_date = d.order_date
  GROUP BY d.order_date
)
SELECT order_date, revenue,
       LAG(revenue) OVER (ORDER BY order_date) AS prev_day_revenue
FROM daily
ORDER BY order_date;

order_date | revenue | prev_day_revenue
-----------+---------+-----------------
2026-03-01 | 2000    | NULL
2026-03-02 | 4000    | 2000
2026-03-03 | 2950    | 4000
2026-03-04 | 0       | 2950
2026-03-05 | 900     | 0
(5 rows)

generate_series is the PostgreSQL and DuckDB way to build dates. In practice most warehouses keep a date dimension table for exactly this join. Practise it: Month-over-month revenue with LAG.

8Consecutive days: the gaps-and-islands trick

"Find each user's longest login streak" sounds like it needs a loop. It does not. The examples use a small logins table with 1 row per user per day:

Number each user's logins in date order, then subtract that number from the date. Dates that are consecutive move up by 1 day while the row number also moves up by one, so the difference stays the same for the whole run. Here are Asha's logins with that difference:

SELECT user_name, login_date,
       ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY login_date) AS rn,
       login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY login_date) AS INT) AS grp
FROM logins
WHERE user_name = 'Asha'
ORDER BY login_date;

user_name | login_date | rn | grp
----------+------------+----+-----------
Asha      | 2026-03-01 | 1  | 2026-02-28
Asha      | 2026-03-02 | 2  | 2026-02-28
Asha      | 2026-03-03 | 3  | 2026-02-28
Asha      | 2026-03-05 | 4  | 2026-03-01
Asha      | 2026-03-06 | 5  | 2026-03-01
(5 rows)

The grp value means nothing as a date. It is only a label that stays fixed through one unbroken run and changes after a gap (Asha skipped 4 March). Group by it and count:

WITH numbered AS (
  SELECT user_name, login_date,
         login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY login_date) AS INT) AS grp
  FROM logins
),
streaks AS (
  SELECT user_name, COUNT(*) AS days
  FROM numbered
  GROUP BY user_name, grp
)
SELECT user_name, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_name
ORDER BY user_name;

user_name | longest_streak
----------+---------------
Asha      | 3
Ravi      | 1
(2 rows)

Two things to say in the interview: deduplicate first if a user can log in twice on 1 day, because a repeated date breaks the arithmetic; and date minus integer is PostgreSQL and DuckDB syntax, while Spark SQL uses date_sub(login_date, rn). Practise it: Longest consecutive login streak, Consecutive login day pairs and Sessionize events with 30-minute gaps (free), which uses the same idea on timestamps.

9Percent of total without a self-join

To show each customer's share of their city's revenue you need 2 levels at once: the customer total and the city total. A window over an aggregate gives both in one pass.

SELECT city, customer, SUM(amount) AS customer_total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (PARTITION BY city), 1) AS pct_of_city
FROM orders
GROUP BY city, customer
ORDER BY city, pct_of_city DESC;

city    | customer | customer_total | pct_of_city
--------+----------+----------------+------------
Chennai | Meena    | 3400           | 57.6
Chennai | Kiran    | 2500           | 42.4
Pune    | Asha     | 2700           | 68.4
Pune    | Ravi     | 1250           | 31.6
(4 rows)

SUM(SUM(amount)) reads oddly the first time. The inner SUM is the GROUP BY aggregate, one value per customer. The outer SUM ... OVER (PARTITION BY city) adds those per-customer values across the city, after grouping has happened. Writing 100.0 rather than 100 keeps the division from becoming integer division in engines that do that. Practise it: Salary percent of department total.

10Moving averages: last 3 rows or last 3 days?

A 3-day moving average is often written with a ROWS frame:

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_last_3_rows
FROM daily
ORDER BY order_date;

order_date | revenue | avg_last_3_rows
-----------+---------+----------------
2026-03-01 | 2000    | 2000.0
2026-03-02 | 4000    | 3000.0
2026-03-03 | 2950    | 2983.3
2026-03-05 | 900     | 2616.7
(4 rows)

The 5 March value averages 2 March, 3 March and 5 March, because those are the last 3 rows. It is the same missing-day problem as LAG. A RANGE frame with an interval counts calendar days instead:

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY order_date
             RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 1) AS avg_last_3_days
FROM daily
ORDER BY order_date;

order_date | revenue | avg_last_3_days
-----------+---------+----------------
2026-03-01 | 2000    | 2000.0
2026-03-02 | 4000    | 3000.0
2026-03-03 | 2950    | 2983.3
2026-03-05 | 900     | 1925.0
(4 rows)

Now 5 March averages only 3 March and 5 March (2950 and 900), because 4 March has no row and 2 March is outside the 3-day range. Whether a day with no orders should count as zero is a business decision; if it should, fill the calendar first as in question 7. Interval frames are supported in PostgreSQL, DuckDB, Oracle and Spark SQL, and the syntax varies, so check your engine. Practise it: 3-day moving average of transactions (free).

A checklist to run before you say "done"

• Does the ORDER BY inside OVER have ties? If yes, did you choose ROW_NUMBER, RANK or DENSE_RANK on purpose, and add a tie-breaker where you need one?
• Are you filtering on a window result? It has to happen in an outer query, a CTE, or QUALIFY.
• Did you rely on the default frame? With duplicate sort values the default RANGE frame groups peers together.
• Are there missing dates? LAG and ROWS frames count rows, not days.
• Did you dedupe before counting streaks or computing changes?

When these answers come quickly, move on to problems that combine two of the patterns, like Session count and length per user or Salary percentile within department.