15 SQL Interview Questions with Solutions

SQL interviews lean on a small number of recurring patterns. Drill these and most onsite questions reduce to a variation you have already seen. Each item below has the kind of prompt a real interviewer uses plus a reference solution.

1. Second highest salary

Return the second highest salary from an employees table. Return NULL if it does not exist.

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

Clean two-line answer. Interviewer who wants to see windowing will ask for the Nth highest; switch to DENSE_RANK for that.

2. Nth highest salary

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT salary FROM ranked WHERE rnk = :n;

3. Top 3 highest-paid per department

SELECT department, employee, salary
FROM (
  SELECT
    department, employee, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

4. Customers who never ordered

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

5. Duplicate emails

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

6. Consecutive days active

Find users who logged in at least three days in a row. This is a gaps-and-islands problem; the classic trick is ROW_NUMBER minus day.

WITH daily AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
grouped AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM daily
),
runs AS (
  SELECT user_id, grp, COUNT(*) AS streak
  FROM grouped
  GROUP BY user_id, grp
  HAVING COUNT(*) >= 3
)
SELECT DISTINCT user_id FROM runs;

7. Rolling 7-day average

SELECT
  day,
  revenue,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d
FROM daily_revenue;

8. Median salary

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;

If PERCENTILE_CONTis unavailable (MySQL < 8), compute the median by averaging the middle values of an ordered set.

9. First and last purchase per customer

SELECT
  customer_id,
  MIN(created_at) AS first_order,
  MAX(created_at) AS last_order
FROM orders
GROUP BY customer_id;

10. Department with the highest average salary

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

11. Employees who earn more than their manager

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

12. Pivot rows into columns

SELECT
  user_id,
  SUM(CASE WHEN action = 'login'   THEN 1 ELSE 0 END) AS logins,
  SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM events
GROUP BY user_id;

13. Running total per customer

SELECT
  customer_id, order_id, total,
  SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running
FROM orders;

14. Delete duplicates, keep the earliest

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

15. Self-join for sequential events

For each session, compute the time to the next event.

SELECT
  e.session_id, e.event_id,
  LEAD(e.created_at) OVER (PARTITION BY e.session_id ORDER BY e.created_at)
    - e.created_at AS gap
FROM events e;

Practice these live

Interview Mode in SQL Protocol has three floors of timed questions modeled on exactly these patterns. The Arena pits you against another candidate on the same problem, which is the closest you can get to a live interview without one.

SQL Interview Questions You Should Drill