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.
The stakes are real. In the 2025 Stack Overflow Developer Survey, SQL was the third most-used language overall at 58.6% of respondents, and 61.3% of professional developers reported working with it. That reach is why analyst, data engineer, and backend interview loops all include a SQL round.
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;PERCENTILE_CONT is what PostgreSQL calls an ordered-set aggregate. If it is 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 drilling the join, aggregation, and subquery patterns above. The Arena pits you against another candidate on the same problem, which is the closest you can get to a live interview without one.