SQL interview questions

SQL rounds draw from a surprisingly small pool. The same ten or so patterns come back again and again, dressed in different tables: the second highest salary, the top three per department, customers who never ordered, duplicates, running totals, streaks. Learn the pattern behind each and you can answer the variations you have never seen.

Every answer below runs on Postgres, and on MySQL 8 except where noted. Each question is followed by the thinking, because the interviewer cares more about that than about the syntax.

First, the order SQL actually runs in#

Half of the confusing errors in SQL come from reading a query top to bottom. The database runs it in this order:

FROM / JOIN  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY  ->  LIMIT

This explains three things interviewers love to ask. WHERE cannot use an aggregate like COUNT(*), because grouping has not happened yet, which is what HAVING is for. WHERE cannot use a column alias from SELECT, because SELECT runs later. And ORDER BY can use the alias, because it runs after.

Window functions: the tool behind half the answers#

A window function computes a value for each row using other rows around it, without collapsing the rows the way GROUP BY does. The ranking functions are the ones that keep coming up, and the difference between them is all about ties.

SELECT name, dept, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS r
FROM employees;

1. The Nth highest salary#

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

DENSE_RANK because "second highest salary" means the second distinct value, however many people share the first. ROW_NUMBER would return whoever happened to come second, possibly someone on the top salary. Change 2 to N and it answers the general question. Say out loud what you return when there is no second salary (this returns no rows; some interviewers want NULL, which SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) gives you).

2. Top three earners in each department#

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r
  FROM employees
) ranked
WHERE r <= 3;

PARTITION BY dept restarts the ranking for each department. You must filter in an outer query, because a window function cannot appear in WHERE (it is computed in SELECT, which runs after WHERE). Ask whether ties at third place should all be included: DENSE_RANK includes them, ROW_NUMBER cuts at exactly three.

3. Customers who never placed an order#

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

The LEFT JOIN version, LEFT JOIN orders o ON ... WHERE o.id IS NULL, is equally correct. Do not use NOT IN (SELECT customer_id FROM orders): a single NULL customer_id makes it return nothing at all. Mentioning that unprompted is worth more than the query itself.

4. Find duplicates, then delete them#

-- find
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- delete all but the oldest of each
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
  ) t
  WHERE rn > 1
);

HAVING because the condition is on a group. ROW_NUMBER for the delete because you want exactly one survivor per email, ties or not. Then say how you would stop it happening again: a unique index on email.

5. A running total#

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

SUM as a window function adds up every row from the start to the current one. Add PARTITION BY store_id for a running total per store.

6. Change from the previous month#

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;

LAG reads the previous row's value, LEAD the next one. The first month has no previous, so its change is NULL, which is the correct answer rather than zero.

7. Longest streak of consecutive login days#

This is the hardest common one, usually called gaps and islands.

WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
grouped AS (
  SELECT user_id, login_date,
         login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS int) AS grp
  FROM days
)
SELECT user_id, COUNT(*) AS streak
FROM grouped
GROUP BY user_id, grp
ORDER BY streak DESC;

The trick: number each user's login days 1, 2, 3 and subtract that many days from the date. For consecutive days, the date and the number both go up by one, so the difference stays the same. A gap breaks the pattern and starts a new group. Count the rows in each group and you have the length of each streak. (That is Postgres, where a date minus a whole number is a date. In MySQL, compute the row number in one CTE and use DATE_SUB(login_date, INTERVAL rn DAY) in the next.)

Quick fire questions#

Checkpoint#