Interview Questions, Part 1
The classics.
If you apply for a data job, you'll almost certainly get an SQL test. The good news is, the same handful of questions come up again and again. You already know everything you need. Let's practise the classics, and the traps hidden inside them. For each one, pause, and try it yourself first.

- Every data job has a SQL test
- The same questions come up
- You already know enough
Second highest salary
The most famous interview question of all. Find the second highest salary. One way, find the highest salary below the maximum. Run it. Fifty six thousand dollars.
SELECT MAX(salary) AS second_highest
FROM staff
WHERE salary < (SELECT MAX(salary) FROM staff);โโโโโโโโโโโโโโโโโโ โ second_highest โ โโโโโโโโโโโโโโโโโโค โ 56000 โ โโโโโโโโโโโโโโโโโโ
The flexible way
The interviewer will often say, now find the third highest. Or the tenth. So here's a version that works for any position. Dense rank numbers the salaries, and we keep number two. Run it. Priya Nair. Dense rank also handles ties properly, which interviewers love.
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM staff
)
SELECT name, salary
FROM ranked
WHERE rnk = 2;โโโโโโโโโโโโโโฌโโโโโโโโโ โ name โ salary โ โโโโโโโโโโโโโโผโโโโโโโโโค โ Priya Nair โ 56000 โ โโโโโโโโโโโโโโดโโโโโโโโโ
Any position
Here's the same pattern for the third most expensive book. Just change the number. Run it. SQL in a Weekend, at twenty nine ninety nine.
WITH ranked AS (
SELECT title, price,
DENSE_RANK() OVER (ORDER BY price DESC) AS rnk
FROM books
)
SELECT title, price
FROM ranked
WHERE rnk = 3;โโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ title โ price โ โโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ SQL in a Weekend โ 29.99 โ โโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
Summary
For the second highest, use max below the max, or dense rank for any position. Next, a self join classic.
Key takeaways- MAX below the MAX
- DENSE_RANK for any N
- Handles ties