Ranking queries answer 'the 2nd highest salary', 'the top order per customer', or 'rank rows within a group'. Window functions — ROW_NUMBER, RANK, DENSE_RANK — compute a ranking over a set of rows (a 'window') without collapsing them like GROUP BY does, and are the modern, reliable way to solve these classic interview problems.
A window function performs a calculation across a set of rows related to the current row, defined by an OVER clause with optional PARTITION BY (reset per group) and ORDER BY (the ranking order). The three ranking functions differ in tie handling: ROW_NUMBER assigns unique sequential numbers even to ties (1,2,3,4), RANK leaves gaps after ties (1,2,2,4), and DENSE_RANK does not (1,2,2,3). This distinction is the crux of 'Nth highest' questions: to get the Nth highest DISTINCT value you use DENSE_RANK = N, whereas ROW_NUMBER = N gives the Nth row which may be wrong when duplicates exist. PARTITION BY enables per-group ranking like 'the most expensive product in each category' or 'each customer's latest order'. Window functions are cleaner and less error-prone than the older correlated-subquery or self-join approaches, and are widely available. They are among the most frequently asked advanced SQL interview topics for SDET roles.
Asked for the 2nd-highest product price in a category (a common interview question), a tester uses DENSE_RANK: rank prices descending per category and select where the rank equals 2 — correctly handling ties where two products share the top price.
-- 2nd-highest DISTINCT price per category (ties handled correctly)
SELECT category, name, price FROM (
SELECT category, name, price,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
FROM products
) t
WHERE rnk = 2;
-- Each customer's most recent order (top row per group)
SELECT user_id, order_id, created_at FROM (
SELECT id AS order_id, user_id, created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE rn = 1;
-- Compare tie behavior on the same data
SELECT name, price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
RANK() OVER (ORDER BY price DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products;Scenario: An interviewer asks for 'the 2nd highest salary in the company'. A candidate writes a query using ROW_NUMBER() OVER (ORDER BY salary DESC) and filters where the number = 2. The interviewer points out it can give the wrong answer. Why, and what's the correct approach?