Classic interview queries, part 1

Why these questions?

Interviewers reuse a small set of questions because each one tests a core skill in a few lines: subqueries, GROUP BY with HAVING, self joins, outer joins and window functions. They care less about the exact answer and more about whether you notice the traps: ties, NULLs and duplicates. Practise saying your thinking out loud while you write.

Second highest salary, three ways

Salaries in COMPANY are monthly. The highest is 250000 and the second highest distinct salary is 120000. "Distinct" matters: if two people shared the top salary, the second highest is still the next different value.

-- 1. MAX below the MAX (works everywhere)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);          -- 120000

-- 2. DISTINCT + LIMIT/OFFSET (SQLite, PostgreSQL, MySQL)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;                        -- 120000

-- 3. DENSE_RANK (also gives names, handles ties)
SELECT name, salary
FROM (SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
      FROM employees) AS ranked
WHERE dr = 2;                                                 -- Rahul Sharma | 120000

Without DISTINCT, way 2 is wrong when the top salary repeats: it would return the top salary again. Way 1 returns NULL when there is no second value, which interviewers often want ("return NULL if there is none"). Way 2 returns no row in that case.

Nth highest

For the Nth highest, use OFFSET N − 1 with DISTINCT, or DENSE_RANK() = N. DENSE_RANK gives tied salaries the same rank and leaves no gaps, so dr = 5 returns both Anita Desai and Vikram Singh (95000). RANK would leave a gap after a tie, and ROW_NUMBER would pick one of them at random, so they answer different questions.

Finding duplicates

Duplicates are rows that should be unique but are not, such as the same email signed up twice. Group by the column that should be unique and keep groups with more than one row.

SELECT email, COUNT(*) AS cnt
FROM signups
GROUP BY email
HAVING COUNT(*) > 1;
-- asha@example.com | 3
-- ravi@example.com | 2

Deleting duplicates but keeping one

DELETE FROM signups
WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY email);
-- keeps ids 1, 2, 4, 6: the first sign-up for each email

Another common answer uses ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and deletes rows numbered above 1. Afterwards, add a UNIQUE constraint so duplicates cannot come back.

Employees who earn more than their manager

This is a self join: the employees table joined to itself, once as the employee (e) and once as the manager (m). In COMPANY nobody out-earns their manager, so the query correctly returns no rows. On a small staff table it finds two people:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM staff e
JOIN staff m ON m.id = e.manager_id
WHERE e.salary > m.salary;
-- Nisha | 98000  | Joseph | 90000
-- Tara  | 160000 | Kiran  | 150000

Top earner per department

SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.dept_id = e.dept_id);
-- Engineering | Meera Iyer     | 250000
-- Sales       | Pooja Gupta    | 110000
-- Finance     | Farhan Ali     | 105000
-- HR          | Lakshmi Pillai | 80000

Departments with no employees

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;            -- Legal

Common mistakes

• Forgetting DISTINCT in the LIMIT/OFFSET answer, so a repeated top salary breaks it.
• Using ROW_NUMBER when the question needs ties kept. Use DENSE_RANK (or RANK) and say why.
• Writing WHERE COUNT(*) > 1. Aggregates go in HAVING.
• Deleting duplicates without checking first. Run the SELECT version, use a transaction, then add a UNIQUE constraint.
• Using NOT IN with a subquery that returns NULL, which makes the whole result empty. Use NOT EXISTS or LEFT JOIN ... IS NULL.