SQL Interview Prep
Part 5 of 5 · Window Functions & Performance
✦ ROW_NUMBER, RANK, DENSE_RANK
✦ LAG, LEAD, FIRST_VALUE, LAST_VALUE
✦ PARTITION BY & frame clauses
✦ Indexing basics
✦ Query optimization & EXPLAIN
Window Functions
Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing
them into a single output row (unlike aggregates). They are indispensable for ranking, running totals, moving
averages, and lead/lag comparisons.
function_name() OVER (
PARTITION BY partition_col -- optional: split into groups
ORDER BY order_col -- required for ranking/offset fns
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- optional frame
)
Ranking Functions
ROW_NUMBER() — Unique sequential integer, no ties.
RANK() — Tied rows get the same rank; next rank skips (1,2,2,4).
DENSE_RANK() — Tied rows same rank; next rank does NOT skip (1,2,2,3).
NTILE(n) — Divides rows into n equal buckets.
PERCENT_RANK() — Relative rank as a value between 0 and 1.
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rn
k
FROM employees;
Offset & Value Functions
LAG(col, n) — Value of col from n rows BEFORE the current row.
LEAD(col, n) — Value of col from n rows AFTER the current row.
FIRST_VALUE(col) — First value in the window frame.
LAST_VALUE(col) — Last value in the window frame (watch frame clause!).
-- Month-over-month revenue change
SELECT month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change
FROM monthly_revenue;
Common Interview Questions
Q: How do you get the top N rows per group?
A: Use ROW_NUMBER() or RANK() in a CTE/subquery, then filter in an outer query.
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER
(PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;
Q: What is the difference between RANK and DENSE_RANK?
A: Both assign the same rank to ties. RANK then skips the next numbers (1,2,2,4). DENSE_RANK does not
skip (1,2,2,3). Use DENSE_RANK when you want consecutive ranking.
Q: How do you compute a 3-day moving average?
A: Use AVG with a ROWS BETWEEN frame clause.
SELECT date, revenue,
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3d
FROM daily_sales;
Query Performance Essentials
Performance questions appear in senior SQL interviews. Know the basics of indexing and how to read an
execution plan.
■ Use indexes on join and filter columns
CREATE INDEX idx_emp_dept ON employees(department);
■ Avoid functions on indexed columns in WHERE
-- Bad: WHERE YEAR(hire_date) = 2023
-- Good: WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
■ Prefer EXISTS over IN for large subqueries
EXISTS stops at the first match; IN collects the full list first.
■ Use EXPLAIN / EXPLAIN ANALYZE
EXPLAIN SELECT * FROM employees WHERE department = 'Eng';
-- Look for: Seq Scan (bad on large tables) vs Index Scan (good)
■ Avoid SELECT *
Fetching unused columns wastes I/O and prevents covering-index scans.
■ Final tip: in interviews, always talk through your query before writing it. Explain your JOIN choices,
why you used a CTE vs subquery, and how you'd add an index if the table were large.
Communication matters as much as syntax.