Six SQL interview questions, worked through

SQL interviews tend to circle the same handful of patterns. Learn the patterns and the questions stop being new. These six are worked on a small dataset; each solution was run and the output is real.

The data

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
 (1, 'Ana', 'Data', 90, NULL), (2, 'Ben', 'Data', 70, 1), (3, 'Cy', 'Data', 95, 1),
 (4, 'Di', 'Eng', 100, NULL),  (5, 'Eli', 'Eng', 80, 4),  (6, 'Fay', 'Eng', 60, 4),
 (7, 'Gus', 'Ops', 50, NULL);

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cy'), (4, 'Di');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, ordered_on TEXT, total INTEGER);
INSERT INTO orders VALUES
 (1, 1, '2025-01-05', 50), (2, 1, '2025-01-20', 70), (3, 2, '2025-02-01', 20),
 (4, 3, '2025-02-02', 90), (5, 3, '2025-03-01', 10);

1. The second-highest salary

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Result: 95. The inner query finds the top salary (100), and the outer takes the highest value below it. Say out loud: this treats equal salaries as one value, so it is the second-highest distinct salary. If two people tie for first and the question wants the second person, the answer changes, so ask. For the Nth highest, use DENSE_RANK() and filter on the rank.

2. The top earner in each department

SELECT department, name, salary
FROM (
    SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
    FROM employees
)
WHERE r = 1
ORDER BY department;

Result: Cy (Data, 95), Di (Eng, 100), Gus (Ops, 50). PARTITION BY restarts the ranking for each department, and RANK() gives tied top earners all rank 1, so ties are kept. Use ROW_NUMBER() instead if you want exactly one row per department. The filter has to sit outside, because you cannot use a window function in WHERE.

3. Customers who never ordered

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

Result: Di. This is an anti-join. A left join keeps every customer, and customers with no matching order have NULL in the order columns, which the WHERE then selects. NOT EXISTS expresses the same thing and is just as good. Avoid NOT IN (subquery) when the subquery can return NULL, because it then returns nothing at all.

4. Employees who earn more than their manager

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Result: Cy earns 95, more than Ana (90). A self-join joins a table to itself under two aliases, one playing the employee and one the manager. The inner join drops people with no manager, which is right here. Say so.

5. Month-over-month change

WITH monthly AS (
    SELECT substr(ordered_on, 1, 7) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

LAG reaches back one row in the ordered result. The first month has nothing before it, so change is NULL. Mention: a month with no orders would be missing from the result rather than shown as zero, and the "previous" row would then be two months back. For reports, join to a calendar table.

6. Departments with a high average salary

SELECT department, ROUND(AVG(salary), 1) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75;

Result: Data 85.0 and Eng 80.0. WHERE filters rows before grouping, and HAVING filters groups after, which is the only place you can test an aggregate. Ops (50) is excluded.

The pattern behind them

How to practise

1. Do each problem on paper first, then run it. Predict the result before you press enter.
2. Change the data: add a tie, add a NULL, add an empty group. See which solutions break, and fix them.
3. Explain each query to someone in two sentences. If you cannot, you have not finished understanding it.
4. Time yourself, but aim first for correct and clear, then for fast.