Phase 8 of 8 · 16 lessons
SQL interview problems, from easy to hard
Solve the classic SQL interview questions from a blank page, explain your approach while you write, and name the edge cases before the interviewer does.
Most SQL interview questions are one of about a dozen patterns: Nth highest, top N per group, rows with no match, duplicates, comparisons with a group average, period-over-period change, first event per user, streaks, retention. Learn to spot the pattern from the wording, and the query almost writes itself.
Lessons, in order
-
8.1
How to answer any SQL interview question
A repeatable method: restate the question, look at the tables and their grain, ask about edge cases, plan the steps out loud, write the query as small CTEs, then check the result against a few rows you can work out by hand.
-
8.2
Quick-fire SQL theory questions
Short "what is the difference between…" questions that open many SQL interviews; each is best answered with a one-line definition of both sides, the key difference, and when you would use each.
-
8.3
The second highest salary (and the Nth)
Return the second highest salary from employees. If there is no second highest salary, return NULL.
-
8.4
Employees who earn more than their manager
Find every employee who earns more than their own manager. Show the employee's name and salary, and the manager's name and salary.
-
8.5
Customers who have never had an order delivered
List every customer who has never had an order delivered — including customers who have never ordered at all.
-
8.6
Find duplicate emails, then remove the duplicates
Find every email that belongs to more than one customer, with how many sign-ups it has. Then delete the duplicates, keeping the row with the lowest customer_id.
-
8.7
The highest-paid employee in each department
For each department, return the employee (or employees, if tied) with the highest salary. Show the department name, the employee's name and their salary.
-
8.8
Employees paid above their department's average
List employees whose salary is above the average salary of their own department, showing that average next to them.
-
8.9
Month-over-month revenue growth
For each month, show the revenue and the percentage change from the previous month, rounded to one decimal place.
-
8.10
Each customer's first order
For each customer who has ordered, show their first order — its id, date and amount — and how many days after signing up they placed it.
-
8.11
Streaks: orders on consecutive days (gaps and islands)
Find every streak where a customer ordered on two or more consecutive days. Show the customer, the first and last day of the streak, and its length in days.
-
8.12
Products bought together
Find every pair of different products that appear in the same order, and count how many orders contain each pair. List each pair once.
-
8.14
Retention: who came back within 30 days?
Of the customers who placed an order, what percentage placed another order within 30 days of their first one?
-
8.15
The median salary
Find the median salary of all employees, and then the median salary in each department.
-
8.16
Customers who bought every product in a category
Find the customers who have bought every product in the Stationery category.