Unit 8: 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.
The big picture
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 this unit
- How to answer any SQL interview question: A repeatable method: restate the question, look at the tables and their grain, ask about edge cases, and plan the steps out loud. Then write the query as small CTEs, and check the result against a few rows you can work out by hand.
- 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.
- The second highest salary (and the Nth): Return the second highest salary from employees. If there is no second highest salary, return NULL.
- 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.
- 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.
- 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.
- 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.
- 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.
- Month-over-month revenue growth: For each month, show the revenue and the percentage change from the previous month, rounded to one decimal place.
- 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.
- 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.
- 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.
- Which products make up 80% of revenue? (share of total): Show each product's revenue, its percentage of total revenue, and the running (cumulative) percentage from the best seller down. Then list the products that bring in the first 80% of revenue.
- 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?
- The median salary: Find the median salary of all employees, and then the median salary in each department.
- Customers who bought every product in a category: Find the customers who have bought every product in the Stationery category.