SQL window function interview questions with runnable answers

All data here is synthetic. Every code block creates its own small table, so each one runs on its own.

The table used in questions 1 to 5

A hypothetical sales team with monthly revenue in rupees. The block below also answers question 1.

CREATE TABLE rep_sales (rep TEXT, region TEXT, month TEXT, revenue INTEGER);
INSERT INTO rep_sales VALUES
 ('Asha','North','2026-01',420000),('Asha','North','2026-02',380000),
 ('Ravi','North','2026-01',420000),('Ravi','North','2026-02',510000),
 ('Meena','North','2026-01',300000),('Meena','North','2026-02',300000),
 ('Kabir','West','2026-01',610000),('Kabir','West','2026-02',450000),
 ('Divya','West','2026-01',390000),('Divya','West','2026-02',470000);

-- Q1: rank reps by January revenue within each region
SELECT rep, region, revenue,
       RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC, rep) AS rn
FROM rep_sales
WHERE month = '2026-01'
ORDER BY region, rnk;

1. Rank each rep within their region by January revenue. How do RANK, DENSE_RANK and ROW_NUMBER differ here?

Asha and Ravi tie at ₹4,20,000 in North. RANK gives both 1 and Meena 3; DENSE_RANK gives Meena 2; ROW_NUMBER forces 1 and 2, and the tie-breaker rep makes that order deterministic. Checking: do you notice the tie before being told, and do you add a tie-breaker so ROW_NUMBER does not return different rows on different runs.

2. Return the top rep per region for January, but show both if two reps tie.

Use RANK (or DENSE_RANK) in a CTE and filter WHERE rnk = 1. ROW_NUMBER would silently drop one of the tied reps. Checking: that "top one" is a business question — you should ask whether ties are both reported before choosing the function.

3. Why can't you write `WHERE RANK() OVER (...) = 1`?

Logical processing order: FROM, WHERE, GROUP BY, HAVING, then SELECT (where windows are computed), then ORDER BY. The window value does not exist yet when WHERE runs. Checking: whether you know the evaluation order rather than memorising the CTE pattern.

4. Running total of revenue per rep across months.

5. Month-on-month change per rep, and the percentage change.

CREATE TABLE rep_sales (rep TEXT, month TEXT, revenue INTEGER);
INSERT INTO rep_sales VALUES
 ('Asha','2026-01',420000),('Asha','2026-02',380000),('Asha','2026-03',450000),
 ('Ravi','2026-01',420000),('Ravi','2026-02',510000),('Ravi','2026-03',0);

SELECT rep, month, revenue,
       SUM(revenue) OVER (PARTITION BY rep ORDER BY month
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       revenue - LAG(revenue) OVER (PARTITION BY rep ORDER BY month) AS mom_change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY rep ORDER BY month))
             / NULLIF(LAG(revenue) OVER (PARTITION BY rep ORDER BY month), 0), 1) AS mom_pct
FROM rep_sales
ORDER BY rep, month;

For question 4, say out loud why you wrote an explicit ROWS frame. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same ORDER BY value as peers and adds them together. With one row per rep per month it gives the same answer; with duplicate months it would not. For question 5, the first month has no previous value so LAG returns NULL, and NULLIF(..., 0) stops a division by zero if a previous month was zero. Checking: frame awareness, and whether you guard the percentage against zero and NULL instead of letting the query error.

Deduplication and gaps, questions 6 to 8

6. A CRM table has duplicate leads per phone number. Keep only the latest record per phone.

CREATE TABLE leads (lead_id INTEGER, phone TEXT, source TEXT, created_at TEXT);
INSERT INTO leads VALUES
 (1,'9800000001','web','2026-09-01 10:00'),
 (2,'9800000001','web','2026-09-01 10:01'),
 (3,'9800000002','ads','2026-09-02 09:00'),
 (4,'9800000003','web','2026-09-03 18:30'),
 (5,'9800000003','referral','2026-09-05 11:15');

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY phone
                               ORDER BY created_at DESC, lead_id DESC) AS rn
  FROM leads)
SELECT lead_id, phone, source, created_at FROM ranked WHERE rn = 1 ORDER BY phone;

Here ROW_NUMBER is correct precisely because you want exactly one row, even if two records share a timestamp. lead_id DESC settles that tie. Checking: the ROW_NUMBER-in-a-CTE pattern, plus whether you ask which record counts as "latest" — created time or updated time.

7. Find each customer's second order. Same pattern with ORDER BY order_date and rn = 2. Mention that customers with one order simply do not appear, and ask whether the report needs them with a NULL.

8. Find days on which a daily job did not run (gaps in a date sequence).

CREATE TABLE job_runs (run_date TEXT);
INSERT INTO job_runs VALUES ('2026-09-01'),('2026-09-02'),('2026-09-05'),('2026-09-06'),('2026-09-09');

SELECT run_date AS last_run_before_gap,
       next_run,
       CAST(julianday(next_run) - julianday(run_date) AS INTEGER) - 1 AS missing_days
FROM (SELECT run_date, LEAD(run_date) OVER (ORDER BY run_date) AS next_run FROM job_runs)
WHERE julianday(next_run) - julianday(run_date) > 1;

The output shows two gaps: two missing days after 2 September and two after 6 September. Checking: use of LEAD to compare a row with its neighbour instead of a self-join, and comfort with date arithmetic in the dialect in front of you.

Moving averages and shares, questions 9 and 10

9. Seven-day moving average of daily signups. Use AVG(signups) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Then volunteer the two caveats: the first six days average fewer than seven values, and if a day is missing from the table, "6 preceding rows" is not "6 preceding days". The fix is to join to a complete calendar first.

10. Each region's share of total revenue, on every row.

CREATE TABLE region_rev (region TEXT, revenue INTEGER);
INSERT INTO region_rev VALUES ('North',1200000),('West',900000),('South',900000);

SELECT region, revenue,
       ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM region_rev
ORDER BY revenue DESC, region;

An empty OVER () makes the whole result the window. North is 40.0%, West and South 30.0% each. Note the 100.0: with two integers, SQLite does integer division and would return 0 for every region. Checking: integer-division awareness, which catches more candidates than any window syntax does.

How to use this bank in practice

Do not memorise the answers. Run each block, then change one thing — add a duplicate month in question 4, remove the tie-breaker in question 6 — and predict the output before you run it again. That habit of predicting and checking is what an interviewer sees when you talk through a query. For joins and the NULL behaviour that breaks many window queries, work through SQL join and NULL interview traps next.

Window functions, CTEs and reconciliation work are practised on realistic business tables in the Data Analyst programme; if you want to see where your current resume stands first, the free skill check gives a quick read.