SQL interview problem set

50 min · Advanced

An SQL interview question is a small business question on two or three tables: "second highest salary", "customers who never ordered", "topper of each course". Twenty of them are here, all on the practice database: six worked examples, eight practice problems and six query-reading questions in the MCQ tab. The interviewer listens to how you reach the answer as much as to the answer itself.

How to answer an SQL problem out loud

The grain of a result is what one row of it means, such as one row per department or one value in total. It decides the GROUP BY, or tells you there is none.

Before any SQL, say what one row of the answer is. Then clarify, plan, build, check and talk, in that order.

Example 1 · Second highest salary, two ways

SQL

1-- Way 1: the highest salary below the top one2SELECT MAX(salary) AS second_highest3FROM employees4WHERE salary < (SELECT MAX(salary) FROM employees);5
6-- Way 2: distinct salaries, skip one, take one7SELECT DISTINCT salary AS second_highest8FROM employees9ORDER BY salary DESC10LIMIT 1 OFFSET 1;

Output

+----------------+
| second_highest |
+----------------+
|      180000.00 |
+----------------+
+----------------+
| second_highest |
+----------------+
|      180000.00 |
+----------------+

What this code does

Say it aloud: "the top salary is 250000; the highest salary below it is the answer." Both ways give 180000.00.
Way 2 needs DISTINCT: if two people shared the top salary, OFFSET 1 without it would return the top salary again.
Edge case: when every salary is the same, Way 1 returns one row with NULL and Way 2 returns no rows. Ask which one the caller expects.

A good answer follows the same five steps every time: ask what the result should look like, name the tables and how they join, build the query one clause at a time, test the edge cases, and say the plan aloud as you write. Talking through it shows the interviewer how you think, and they can help you when you get stuck.

The first question to settle is the grain of the result: what one row means. "One row per department", "one row per customer who never ordered", "one value". Then check the edge cases out loud: ties (two people with the same salary), NULLs (an employee with no department), and an empty answer (only one salary in the table). Example 1 is the most asked SQL question of all, solved two ways, and the two ways behave differently on an edge case. Aloud, the steps sound like this. Clarify: "Second highest distinct salary, or second row? What if there is none?" Plan: "One table, one value, so no GROUP BY." Build: write the inner part first, run it, then wrap it. Check: ties, NULLs and empty results, and say what your query returns for each.

Join and grouping patterns

To count per group and keep empty groups, use a LEFT JOIN from the group table and COUNT(column) on the joined table. COUNT(*) would also count the NULL row the join adds for an empty group.

Most questions are one of a few patterns. Counting per group while still showing empty groups is the commonest.

Employees per department with a LEFT JOIN. Engineering has 5 employees, Sales 3, HR 2, Finance 2 and Research none. For Research the join adds one row of NULLs: COUNT(*) would say 1, but COUNT of the employee column says 0.

Example 2 · Employees per department, zeros included

SQL

SELECT d.dept_name, COUNT(*) AS wrong_count, COUNT(e.emp_id) AS employeesFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_id, d.dept_nameORDER BY d.dept_id;

Output

+-------------+-------------+-----------+
| dept_name   | wrong_count | employees |
+-------------+-------------+-----------+
| Engineering |           5 |         5 |
| Sales       |           3 |         3 |
| HR          |           2 |         2 |
| Finance     |           2 |         2 |
| Research    |           1 |         0 |
+-------------+-------------+-----------+

What this code does

The LEFT JOIN keeps Research even though no employee matches it; its employee columns are NULL.
COUNT(*) counts that NULL row and says 1; COUNT(e.emp_id) skips NULLs and gives the true 0.
Tanvi Kulkarni has no department, so she is not in any group here; asking "count unassigned employees too?" is a good clarifying question.

Example 3 · Managers and their direct reports (self join)

SQL

SELECT m.name AS manager, COUNT(*) AS direct_reportsFROM employees eJOIN employees m ON m.emp_id = e.manager_idGROUP BY m.emp_id, m.nameORDER BY direct_reports DESC, m.name;

Output

+---------------+----------------+
| manager       | direct_reports |
+---------------+----------------+
| Vikram Rao    |              5 |
| Anita Desai   |              3 |
| Neha Joshi    |              2 |
| Gita Reddy    |              1 |
| Lakshmi Menon |              1 |
+---------------+----------------+

What this code does

e is the employee and m is the same table playing the manager; the join matches each employee to the row of their manager.
Grouping by the manager gives one row per manager; people who manage nobody never appear as m, so they are not listed.
The same self join answers "employees who earn more than their manager" with WHERE e.salary > m.salary (no one does in this company).

Recognising the pattern is half the answer. For "count per group, zeros included", Example 2 shows both counts side by side; Research has no employees.

"Compare with a related row in the same table", such as employees and their managers, is a self join: the same table twice under two aliases. Example 3 counts each manager's direct reports.

Order of execution

Build the count per department

Put the lines of the query in the order SQL needs them.

Subquery and window function patterns

A window function calculates a value for each row from a set of related rows without collapsing them, for example RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC).

"Top N per group" and "change from the previous row" keep every row and add a rank or a previous value.

Example 4 · Top earner of each department, ties kept

SQL

1WITH ranked AS (2  SELECT d.dept_name, e.name, e.salary,3         RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk4  FROM employees e5  JOIN departments d ON d.dept_id = e.dept_id6)7SELECT dept_name, name, salary8FROM ranked9WHERE rnk = 110ORDER BY dept_name, name;

Output

+-------------+---------------+-----------+
| dept_name   | name          | salary    |
+-------------+---------------+-----------+
| Engineering | Vikram Rao    | 250000.00 |
| Finance     | Gita Reddy    | 140000.00 |
| HR          | Lakshmi Menon |  90000.00 |
| Sales       | Neha Joshi    | 150000.00 |
+-------------+---------------+-----------+

What this code does

PARTITION BY e.dept_id restarts the ranking in every department; ORDER BY e.salary DESC puts the highest first.
A window function cannot be used in WHERE of the same SELECT, so the CTE computes the rank and the outer query filters on it.
With RANK, two people sharing the top salary would both get rank 1 and both be listed; ROW_NUMBER would keep only one of them.

Example 5 · Month-over-month revenue growth with LAG

SQL

1WITH monthly AS (2  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,3         SUM(oi.quantity * oi.unit_price) AS revenue4  FROM orders o5  JOIN order_items oi ON oi.order_id = o.order_id6  WHERE o.status <> 'CANCELLED'7  GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')8)9SELECT month, revenue,10       LAG(revenue) OVER (ORDER BY month) AS prev_month,11       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))12             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct13FROM monthly14ORDER BY month;

Output

+---------+----------+------------+------------+
| month   | revenue  | prev_month | growth_pct |
+---------+----------+------------+------------+
| 2025-01 | 22544.00 |       NULL |       NULL |
| 2025-02 | 66037.00 |   22544.00 |      192.9 |
| 2025-03 | 72083.00 |   66037.00 |        9.2 |
| 2025-04 | 24594.00 |   72083.00 |      -65.9 |
| 2025-05 |  6298.00 |   24594.00 |      -74.4 |
| 2025-06 |  4897.00 |    6298.00 |      -22.2 |
+---------+----------+------------+------------+

What this code does

The CTE builds one row per month (the grain), leaving out cancelled orders; the outer query only adds the comparison.
LAG(revenue) is the previous row's revenue in month order; January has no previous month, so it gets NULL and so does its growth.
A likely follow-up is "what if a month has no orders?": the month is simply missing, so LAG would compare with an older month. A calendar table of all months fixes that.

For "top per group", rank inside each group with RANK() OVER (PARTITION BY group ORDER BY value DESC) in a CTE, then keep rank 1 outside. RANK keeps ties, which interviewers often ask about. Example 4 finds the top earner of each department.

For "growth", LAG(value) OVER (ORDER BY month) gives the previous month's value in the same row, so the change and the percentage are one subtraction and one division away. Example 5 computes month-over-month growth of the shop's revenue.

Predict the output

Predict the rank of a tie

Karthik Raj and Pooja Gupta both earn 120000. What ranks do they get?

SELECT name, RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employees

WHERE dept_id = 1 AND salary = 120000;

Follow-up questions and the NOT IN trap

The NOT IN trap: if the subquery returns even one NULL, x NOT IN (...) is never true, so the query returns no rows at all. NOT EXISTS does not have this problem.

After a correct answer comes a follow-up: ties, NULLs, speed. Show the interviewer where your query would break.

Example 6 · The NOT IN trap with NULL, and the fix

SQL

1-- The trap: manager_id has a NULL, so NOT IN finds nobody2SELECT COUNT(*) AS not_managers_found FROM employees3WHERE emp_id NOT IN (SELECT manager_id FROM employees);4
5-- The fix: NOT EXISTS is not affected by the NULL6SELECT e.name FROM employees e7WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)8ORDER BY e.emp_id;

Output

+--------------------+
| not_managers_found |
+--------------------+
|                  0 |
+--------------------+
+----------------+
| name           |
+----------------+
| Karthik Raj    |
| Pooja Gupta    |
| Suresh Babu    |
| Amit Shah      |
| Farah Ali      |
| Rohan Das      |
| Manoj Pillai   |
| Tanvi Kulkarni |
+----------------+

What this code does

Vikram Rao has no manager, so the subquery returns a NULL among the ids, and emp_id NOT IN (2, 1, NULL, ...) is never true: 0 rows.
NOT EXISTS asks "is there a row whose manager is this employee?" and gets a plain yes or no, so it lists the 8 people who manage nobody.
Another fix is WHERE manager_id IS NOT NULL inside the NOT IN subquery; saying both fixes is a strong answer.

The most famous NULL follow-up is NOT IN. It fails because x <> NULL is unknown, never true, so one NULL in the subquery removes every row. employees.manager_id is NULL for the CEO, so "employees who manage nobody" with NOT IN finds nobody.

For speed questions, name the index that helps: the columns in WHERE, JOIN and ORDER BY of the query, such as employees (dept_id, salary) for "top earner per department". Then say you would confirm it with EXPLAIN.

Fix the error

Employees who manage nobody

The query returns no rows, but several employees manage nobody. Pick the fixed line.

manager_id is NULL for the CEO.

SELECT name FROM employees

WHERE emp_id NOT IN (SELECT manager_id FROM employees);

Error

Empty set (0.00 sec)

When you are not sure whether a column can be NULL, use NOT EXISTS for "no match" questions. It is always correct, and from MySQL 8.0.17 both forms usually run as the same anti-join plan.

Next step

Check your understanding

12 quick questions

1-- Way 1: the highest salary below the top one2SELECT MAX(salary) AS second_highest3FROM employees4WHERE salary < (SELECT MAX(salary) FROM employees);5 6-- Way 2: distinct salaries, skip one, take one7SELECT DISTINCT salary AS second_highest8FROM employees9ORDER BY salary DESC10LIMIT 1 OFFSET 1;

Join and grouping patterns

To count per group and keep empty groups, use a LEFT JOIN from the group table and COUNT(column) on the joined table. COUNT(*) would also count the NULL row the join adds for an empty group.

Most questions are one of a few patterns. Counting per group while still showing empty groups is the commonest.

+-------------+-------------+-----------+ | dept_name | wrong_count | employees | +-------------+-------------+-----------+ | Engineering | 5 | 5 | | Sales | 3 | 3 | | HR | 2 | 2 | | Finance | 2 | 2 | | Research | 1 | 0 | +-------------+-------------+-----------+

+---------------+----------------+ | manager | direct_reports | +---------------+----------------+ | Vikram Rao | 5 | | Anita Desai | 3 | | Neha Joshi | 2 | | Gita Reddy | 1 | | Lakshmi Menon | 1 | +---------------+----------------+

1WITH ranked AS (2 SELECT d.dept_name, e.name, e.salary,3 RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rnk4 FROM employees e5 JOIN departments d ON d.dept_id = e.dept_id6)7SELECT dept_name, name, salary8FROM ranked9WHERE rnk = 110ORDER BY dept_name, name;

+-------------+---------------+-----------+ | dept_name | name | salary | +-------------+---------------+-----------+ | Engineering | Vikram Rao | 250000.00 | | Finance | Gita Reddy | 140000.00 | | HR | Lakshmi Menon | 90000.00 | | Sales | Neha Joshi | 150000.00 | +-------------+---------------+-----------+

1WITH monthly AS (2 SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,3 SUM(oi.quantity * oi.unit_price) AS revenue4 FROM orders o5 JOIN order_items oi ON oi.order_id = o.order_id6 WHERE o.status <> 'CANCELLED'7 GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')8)9SELECT month, revenue,10 LAG(revenue) OVER (ORDER BY month) AS prev_month,11 ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month))12 / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct13FROM monthly14ORDER BY month;

+---------+----------+------------+------------+ | month | revenue | prev_month | growth_pct | +---------+----------+------------+------------+ | 2025-01 | 22544.00 | NULL | NULL | | 2025-02 | 66037.00 | 22544.00 | 192.9 | | 2025-03 | 72083.00 | 66037.00 | 9.2 | | 2025-04 | 24594.00 | 72083.00 | -65.9 | | 2025-05 | 6298.00 | 24594.00 | -74.4 | | 2025-06 | 4897.00 | 6298.00 | -22.2 | +---------+----------+------------+------------+

1-- The trap: manager_id has a NULL, so NOT IN finds nobody2SELECT COUNT(*) AS not_managers_found FROM employees3WHERE emp_id NOT IN (SELECT manager_id FROM employees);4 5-- The fix: NOT EXISTS is not affected by the NULL6SELECT e.name FROM employees e7WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id)8ORDER BY e.emp_id;