SQL Window Functions Interview Questions with Examples (2026)

SQL window functions interview questions test whether you can compute per-row values, such as ranks, running totals, and previous-row comparisons, across a group of related rows without collapsing them. Almost every one reduces to a few building blocks: PARTITION BY, ORDER BY, a frame, and the right function (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, or an aggregate). This guide works through ten classic problems with the query and the result table for each, so you can see exactly what every step produces.

Key Takeaways

• A window function is any function followed by OVER (...). It adds a column to each row instead of collapsing rows like GROUP BY.
• ROW_NUMBER, RANK, and DENSE_RANK differ only on ties: 1,2,3,4, 1,2,2,4, and 1,2,2,3. Say which one the question needs before you write it.
• With ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Duplicates in the sort key break naive running totals.
• You cannot filter on a window function in WHERE. Wrap it in a CTE, or use QUALIFY on engines that support it.
• Gaps and islands reduces to one trick: value - ROW_NUMBER() is constant inside a consecutive run.
• Sessionization and retention are both "LAG, flag, then running sum or MIN over partition" problems.

What Is a SQL Window Function?

A window function is a calculation over a set of rows related to the current row, returned as a new column on every row. The set of rows is the window. Unlike GROUP BY, the input rows stay in the output, so you can show an employee's salary next to their department's average in the same row.

The full syntax looks like this:

function_name(args) OVER (
  PARTITION BY partition_cols
  ORDER BY sort_cols
  ROWS | RANGE BETWEEN frame_start AND frame_end
)

Each part answers one question:

Interviewers often ask where window functions run in query order. They are computed after FROM, WHERE, GROUP BY, and HAVING, and before ORDER BY and LIMIT. That is why a window function can wrap an aggregate (SUM(COUNT(*)) OVER ()) but cannot appear in WHERE. The PostgreSQL window function tutorial documents this ordering and the default frame clearly, and the behavior is the same in other major engines.

ROWS vs RANGE frames

ROWS counts physical rows. RANGE groups rows by the value of the ORDER BY column, so rows with the same value (peers) are always included together. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it." RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW means "every row whose date is within two days of this one," which matters when dates have gaps. PostgreSQL 11 and later support offset RANGE frames on dates; some engines restrict RANGE offsets to numeric types.

ROW_NUMBER vs RANK vs DENSE_RANK

These three ranking functions are the most asked window functions, and the only difference between them is tie handling. Here is the sample table used in the first problems (salaries in thousands):

SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees;

Notice that Ben and Cai tie at 140. ROW_NUMBER splits them arbitrarily, and the order can change between runs unless you add a tiebreaker like ORDER BY salary DESC, emp_id. RANK skips to 4 after the tie. DENSE_RANK continues at 3.

Use this decision rule:

Problem 1: Top-N per Group

Prompt: Return the top two earners in each department.

This is the single most common window function interview question. LeetCode 185, "Department Top Three Salaries," is the same pattern. The trap is ties, so ask whether "top two" means two people or the top two salary values.

WITH ranked AS (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE drnk <= 2;

With ROW_NUMBER instead, Eng returns only Ana and one of Ben or Cai. On Snowflake, BigQuery, Databricks, or DuckDB you can skip the CTE with QUALIFY drnk <= 2. PostgreSQL and MySQL do not support QUALIFY, so the CTE version is the safe default in an interview.

Problem 2: Nth Highest Salary

Prompt: Find the second highest distinct salary, or NULL if there is none (LeetCode 176).

SELECT MAX(salary) AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
) t
WHERE drnk = 2;

Distinct salaries in order are 150, 140, 120, 90, 85, 70, so the answer is 140. Wrapping in MAX guarantees one row with NULL when no second salary exists. ROW_NUMBER would be wrong here: it would also return 140 on this data, but on a table with two people at 150 it would return 150.

Problem 3: Deduplicate and Keep the Latest Row

Prompt: A user_emails table has several rows per user. Keep only the most recent one.

WITH ordered AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_emails
)
SELECT user_id, email, updated_at
FROM ordered
WHERE rn = 1;

The filter keeps the two rn = 1 rows. This is routine work for data engineers, who use it to clean change-data-capture feeds, so it is a staple of data engineering interviews. Here ROW_NUMBER is correct because you want exactly one row, even if two updates share a timestamp.

Running Totals and Moving Averages

Aggregates become running calculations as soon as you add ORDER BY inside OVER. The next problems use this sales_daily table. Note that 2026-09-04 is missing.

Problem 4: Running total

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales_daily;

Why write the frame explicitly? Because the default is RANGE, which treats duplicate sort values as one block. If an orders table has two orders on the same day (10 and 20) followed by one of 5, SUM(amount) OVER (ORDER BY day) returns 30, 30, 35. With ROWS you get 10, 30, 35.

Problem 5: Three-day moving average

SELECT day, amount,
       ROUND(AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_rows,
       ROUND(AVG(amount) OVER (ORDER BY day RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW), 2) AS ma_range
FROM sales_daily;

The two columns diverge on 2026-09-05. ROWS averages the previous three rows (50, 80, 120) even though they span five calendar days. RANGE looks back two calendar days and only finds 09-03 and 09-05. Neither treats the missing day as zero. If the business wants zeros, build a date spine with generate_series and LEFT JOIN the sales onto it first.

LAG and LEAD: Comparing a Row to Its Neighbors

LAG(col, n, default) returns the value n rows before the current row in the window order, and LEAD returns the value n rows after. Both default to n = 1 and return NULL when there is no such row unless you pass a default. Use them for period-over-period change, time between events, and detecting state changes.

Problem 6: Day-over-day change

SELECT day, amount,
       LAG(amount) OVER (ORDER BY day) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY day) AS change
FROM sales_daily;

Watch the 09-05 row. LAG compares to the previous row, which is 09-03, not the previous calendar day. If the question says "compared with yesterday," either use a date spine or self-join on day - 1. For percent change, divide by NULLIF(LAG(amount) OVER (ORDER BY day), 0) to avoid division by zero.

Problem 7: Share of group total

Prompt: Show each employee's salary as a percentage of their department's total, and how far they are from the department average.

SELECT name, dept, salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept,
       ROUND(salary - AVG(salary) OVER (PARTITION BY dept), 1) AS vs_avg
FROM employees;

No ORDER BY inside OVER means the frame is the whole partition, so every Eng row sees the total of 550 and average of 137.5. Multiply by 100.0 rather than 100 so integer division does not truncate to zero in PostgreSQL and SQL Server.

How Do You Solve Gaps and Islands in SQL?

Gaps and islands problems ask you to find runs of consecutive values, such as login streaks or uninterrupted uptime.

Problem 8: Longest login streak

The trick: subtract ROW_NUMBER() days from each date. Inside a consecutive run, the date and the row number both go up by one, so the result stays fixed and becomes a group key.

WITH d AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
g AS (
  SELECT user_id, login_date,
         login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
  FROM d
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM g
GROUP BY user_id, grp
ORDER BY days DESC;

Step one, the intermediate g table:

Step two, grouped:

The DISTINCT step matters. Two logins on the same day would give that date two row numbers and split the island. The date - integer syntax is PostgreSQL; use DATEADD(day, -rn, login_date) in SQL Server or Snowflake and DATE_SUB in MySQL. LeetCode 180, "Consecutive Numbers," is the same idea on integer ids.

Problem 9: Sessionization

Prompt: Group each user's events into sessions, where a gap of more than 30 minutes starts a new session.

This is gaps and islands on timestamps: LAG, a flag, then a running sum.

WITH flagged AS (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) <= INTERVAL '30 minutes'
              THEN 0 ELSE 1 END AS new_session
  FROM events
)
SELECT user_id, ts,
       SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
FROM flagged;

The first row has a NULL gap, so the comparison is not true and the ELSE branch marks it as a new session. The same flag-and-sum template answers "group rows until the value changes" questions.

Problem 10: Cohort Retention

Prompt: For each signup cohort (first active month), what share of users were active N months later?

Retention is the classic product-analytics question in data scientist interviews. MIN() OVER (PARTITION BY user_id) assigns each activity row its cohort without a separate join.

WITH a AS (
  SELECT user_id, activity_month,
         MIN(activity_month) OVER (PARTITION BY user_id) AS cohort
  FROM activity
),
b AS (
  SELECT cohort, user_id,
         (EXTRACT(YEAR FROM activity_month) - EXTRACT(YEAR FROM cohort)) * 12
       + (EXTRACT(MONTH FROM activity_month) - EXTRACT(MONTH FROM cohort)) AS month_n
  FROM a
)
SELECT cohort, month_n,
       COUNT(DISTINCT user_id) AS users,
       ROUND(100.0 * COUNT(DISTINCT user_id)
             / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort ORDER BY month_n), 1) AS retention_pct
FROM b
GROUP BY cohort, month_n
ORDER BY cohort, month_n;

Two details earn credit here. First, FIRST_VALUE runs over the grouped result, which shows you know window functions execute after GROUP BY. Second, January's month-2 retention is 100% because user 2 came back after skipping February. If the interviewer wants "rolling" retention (active in month N or later) or "consecutive" retention, the definition changes the query, so clarify before writing. The February cohort has no month-2 row because the data ends in March, and you should say that out loud rather than report it as 0%.

Common Window Function Mistakes in Interviews

Most failed answers come from a short list of errors. Check your query against it before saying "done":

1. Filtering in WHERE. WHERE ROW_NUMBER() OVER (...) = 1 is a syntax error. Use a CTE.
2. Nondeterministic ROW_NUMBER. Without a unique tiebreaker, results can change between runs.
3. LAST_VALUE surprise. With the default frame, LAST_VALUE returns the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
4. RANGE peers in running totals. Duplicate sort keys share one cumulative value.
5. Rows vs calendar days. LAG and ROWS frames ignore missing dates.
6. Integer division. salary / total can return 0. Multiply by 100.0 first.
7. Missing PARTITION BY. A ranking without it ranks across the whole table, which silently answers a different question.

The full list of functions you should know is short. The PostgreSQL window functions reference lists them: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE, plus any aggregate used with OVER.

How to Answer a Window Function Question Live

Interviewers grade the process as much as the query. A five-step routine keeps you from rushing into the wrong function:

1. Restate the grain. "One row per department per rank" or "one row per user session."
2. Name the partition and the order. Most bugs come from getting one of these wrong.
3. Decide on ties and frames out loud. "I'll use DENSE_RANK because two people at the same salary should both count."
4. Build in CTEs. Compute the window column first, then filter or aggregate in the next step. It is easier to debug and to explain.
5. Trace a tiny example. Walk three or four rows through by hand, exactly like the tables above.

Talking through steps 2 and 3 is the same skill covered in our guide on how to think out loud in a coding interview. If you go blank, the recovery tactics in what to do when stuck in a coding interview apply to SQL rounds too.

Practice Problems

Try these without looking back. Each maps to one of the ten patterns above.

For broader coverage of joins, NULL handling, indexes, and transactions, pair this with our main SQL interview questions guide. If your SQL round arrives as a timed screen, the online assessment tips cover pacing and test-case strategy. Backend candidates should also expect lighter versions of these problems, as described in the backend engineer interview guide.