Classic SQL Interview Problems

A curated set of the questions that come up again and again in SQL interviews — each builds directly on a concept from earlier lessons in this course.

1. Second-Highest Salary

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

A more general "Nth highest" version, using a window function (see the Window Functions lesson):

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

DENSE_RANK handles ties correctly — two employees tied for highest salary still both count as rank 1, so rank 2 is genuinely the next distinct salary.

2. Find Duplicate Rows

SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

3. Delete Duplicate Rows, Keeping One

DELETE FROM employees
WHERE id NOT IN (
  SELECT MIN(id) FROM employees GROUP BY email
);

4. Department With the Highest Average Salary

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;

5. Employees Earning More Than Their Manager

A self join (see the Joins lesson) is the standard tool for hierarchical comparisons like this.

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

6. Running Total of Sales

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

7. Top 3 Highest-Paid Employees Per Department

The classic "top N per group" — solved with either a window function or a LATERAL join (see both dedicated lessons):

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

8. Find Users Active on Consecutive Days

A "gaps and islands" problem — subtracting a row number from the date collapses consecutive dates into the same group.

WITH activity AS (
  SELECT user_id, login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::INTEGER AS grp
  FROM user_logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS streak_length
FROM activity
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

Within one unbroken run of consecutive dates, date - row_number produces the same constant value — that's the trick that makes GROUP BY grp collapse each streak into one row.

9. Pivot Monthly Totals Into Columns

SELECT
  product_id,
  SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 1) AS jan,
  SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 2) AS feb,
  SUM(amount) FILTER (WHERE EXTRACT(MONTH FROM sale_date) = 3) AS mar
FROM sales
GROUP BY product_id;

Common Errors & Troubleshooting

• Using LIMIT 1 OFFSET 1 for "second highest" and getting a wrong answer with ties — this approach silently breaks when the highest value is tied across multiple rows; DENSE_RANK() (shown above) handles ties correctly, plain OFFSET doesn't.
• The "consecutive days" gaps-and-islands query returning odd groupings — double-check login_date is a true DATE type, not a TIMESTAMP with varying times — subtracting a row number from a timestamp with non-midnight times breaks the "same constant value" trick the pattern relies on.
• A self-join for "employees earning more than their manager" missing the top-level manager — this is expected: whoever has no manager_id (the top of the org chart) has no manager to compare against and is correctly excluded by an INNER JOIN — use a LEFT JOIN if you need them included with a NULL manager instead.

Key Takeaways

• Most "hard" SQL interview questions are really 2-3 techniques from earlier lessons (window functions, self joins, FILTER) combined in a new way.
• DENSE_RANK() is the reliable way to handle "Nth highest value" questions correctly when ties are possible.
• The "gaps and islands" pattern (date - ROW_NUMBER()) is worth memorizing — it comes up in disguise across many consecutive-streak problems.