AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 1
SQL ยท Lesson 93 of 126

Interview Questions, Part 1

The classics.

โฑ 2 min readโ–ถ Video at 2:13:04๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Interview Questions, Part 1
From the video: Interview Questions, Part 1
  • 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.

query.sqlsql
SELECT MAX(salary) AS second_highest
FROM staff
WHERE salary < (SELECT MAX(salary) FROM staff);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚      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
Next up: Employees and Managers โ€” the self join question