SQL Subquery & Window Function Questions

Why interviewers ask this

By the time a SQL interview reaches subqueries and window functions in depth, the question usually isn't "does this return the right answer," it's "does this scale, and do you know why." A correlated subquery and a window function can produce identical output while differing enormously in how many times the database actually touches the data. This guide is built around exactly that distinction.

The first five questions get full treatment: working query, a correlated-subquery alternative, and what changes between them. The remaining five are tighter: one working example and the insight it tests.

How interviewers read a subquery/window function answer

1How do you compare each row to the previous row, like calculating month-over-month change?

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

LAG looks back one row (or more, with a second argument) within the ordered result. The pre-window-function alternative uses a self-join on a calculated date offset:

-- Works, but needs a join condition tied to date arithmetic
SELECT curr.month, curr.revenue,
       prev.revenue AS previous_month_revenue,
       curr.revenue - prev.revenue AS change
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev ON prev.month = curr.month - INTERVAL 1 MONTH;

The self-join version breaks the moment the date logic doesn't line up perfectly (a missing month, an off-by-one in the interval), while LAG works purely on row order and doesn't care about gaps in the underlying dates.

Evaluates: whether the candidate reaches for LAG/LEAD instead of a self-join for row-to-row comparisons, which is both simpler to write and more robust to gaps in the data.

2How do you calculate a moving average over the last 3 rows?

SELECT order_date, revenue,
       AVG(revenue) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3day
FROM daily_revenue;

Without a frame clause, AVG() OVER (ORDER BY order_date) averages every row up to the current one, a running average, not a moving one. The frame clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW restricts the window to exactly the current row and the two before it.

Evaluates: whether the candidate knows the default window frame isn't what most "moving average" questions actually want, and can write an explicit frame clause to fix it. This is the most commonly missed detail in window function interviews.

3How do you calculate each row's percentage of the grand total?

SELECT product, revenue,
       revenue / SUM(revenue) OVER () AS pct_of_total
FROM product_revenue;

SUM(revenue) OVER () with an empty OVER() computes the grand total across every row while still returning one row per product. The pre-window alternative needs a subquery to get the total, then joins it back:

SELECT p.product, p.revenue,
       p.revenue / t.total AS pct_of_total
FROM product_revenue p
CROSS JOIN (SELECT SUM(revenue) AS total FROM product_revenue) t;

Both are correct, but the window function version avoids the extra CROSS JOIN and reads as a single, self-contained query.

Evaluates: knowing that an empty OVER() treats the entire result set as one partition, which is a compact way to blend a per-row value with an aggregate across all rows, without a separate subquery.

4How do you show each employee's salary alongside their department's average, in the same row?

SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

The correlated-subquery alternative recalculates the department average once for every single employee row:

-- Re-runs the AVG calculation once per row
SELECT name, department, salary,
       (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department) AS dept_avg_salary
FROM employees e;

For a department with 50 employees, the correlated version recomputes the same average 50 times. AVG() OVER (PARTITION BY department) computes it once per department and applies it across all matching rows in a single pass.

Evaluates: this is the clearest example of the theme running through this whole guide. A correlated subquery and a window function can return identical results while doing dramatically different amounts of work, and recognising that gap is the actual signal being tested.

5How do you split rows into equal-sized buckets, like quartiles?

SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

NTILE(4) divides the ordered rows into four groups as evenly as possible. The manual alternative calculates row position and buckets it with CASE:

-- Works, but requires computing percentile boundaries manually first
SELECT name, salary,
       CASE
           WHEN row_num <= total_rows * 0.25 THEN 1
           WHEN row_num <= total_rows * 0.50 THEN 2
           WHEN row_num <= total_rows * 0.75 THEN 3
           ELSE 4
       END AS salary_quartile
FROM (
    SELECT name, salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
           COUNT(*) OVER () AS total_rows
    FROM employees
) ranked;

Evaluates: knowing NTILE exists and does this in one function call. This question is often used specifically to see whether a candidate reaches for the built-in tool or reconstructs its logic from scratch, which is a strong efficiency and library-fluency signal at the advanced level.

6What's the difference between EXISTS and IN, and why does it matter with NULLs?

-- EXISTS: safe with NULLs, often faster since it can stop at the first match
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);


-- NOT IN: silently returns zero rows if the subquery contains even one NULL
SELECT name FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

Evaluates: knowing that NOT IN breaks silently (returns no rows at all) if the subquery's result set contains a NULL, while NOT EXISTS doesn't have this problem. This is a well-known trap specifically because it fails quietly rather than throwing an error.

7What's a derived table, and when would you use one?

SELECT dept_summary.department, dept_summary.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_salary > 60000;

Evaluates: knowing a subquery in the FROM clause (a derived table) lets you filter or join on an aggregated result, something you can't do directly in a single-level WHERE clause because aggregates aren't available there until after grouping.

8How do you find what percentile a specific value falls into?

SELECT name, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS percentile
FROM employees;

Evaluates: familiarity with PERCENT_RANK (and its close relative CUME_DIST) for percentile-style questions, which come up often in data and analytics-adjacent interviews specifically.

9How do you use a correlated subquery inside an UPDATE statement?

UPDATE employees e
SET e.bonus = (
    SELECT AVG(salary) * 0.05
    FROM employees e2
    WHERE e2.department = e.department
);

Evaluates: recognising that correlated subqueries aren't limited to SELECT, they work the same way inside UPDATE, recalculating per row being updated. Candidates who've only practiced them in SELECT sometimes freeze here.

10How do you get the first and last value within each group, in the same row?

SELECT name, department, salary,
       FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid_in_dept,
       LAST_VALUE(name) OVER (
           PARTITION BY department ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS lowest_paid_in_dept
FROM employees;

Evaluates: knowing LAST_VALUE needs an explicit frame clause (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to see the whole partition; without it, the default frame stops at the current row, and LAST_VALUE just returns the current row's own value, a subtle bug that surprises even experienced candidates the first time they hit it.

How should you prepare for advanced SQL interviews?

1. Get the frame clause default memorised. PARTITION BY/ORDER BY alone gives a running calculation up to the current row, not the whole partition. Half the mistakes in this guide trace back to that one default.
2. Practise spotting correlated subqueries on sight. If a subquery references a column from the outer query, it re-runs per row. Being able to say that immediately, and name the window-function alternative, is the core skill this entire guide is built around.
3. Don't skip NULL-safety questions. NOT IN versus NOT EXISTS is a small detail with an outsized failure mode, and it's a favourite because it separates candidates who've been burned by it from those who haven't.

Frequently asked questions

Who is this guide for, and what SQL experience does it assume? This is built for candidates preparing for advanced or senior interviews who already know basic joins, GROUP BY, and simple ranking with RANK/ROW_NUMBER. If those are unfamiliar, start with the Top SQL Interview Questions guide first.

Are window functions actually used in real production code, or mostly in interviews? Extensively in real code, particularly in analytics, reporting, and dashboard queries. Running totals, rankings, and period-over-period comparisons are common enough that window function fluency has real day-to-day value beyond interview prep.

What's the single most commonly missed detail in this topic? The default window frame. Most candidates know PARTITION BY and ORDER BY but don't realise the frame silently defaults to "start of partition to current row" unless an explicit ROWS BETWEEN clause overrides it.

Should I always prefer a window function over a correlated subquery? Whenever the two would produce the same result, yes, since the window function typically computes it in one pass instead of once per row. Correlated subqueries still have valid uses, particularly with EXISTS/NOT EXISTS, where no window-function equivalent applies.

How is this different from the general SQL interview guide? The Top SQL Interview Questions guide introduces RANK, DENSE_RANK, and basic correlated subqueries. This guide goes further into frame clauses, LAG/LEAD, NTILE, and the subtler NULL-handling and re-execution issues that come up at a more advanced level.

Related tutorials: Top SQL Interview Questions · SQL JOIN Interview Questions · SQL Query Practice Questions