This is a suggested learning order, not a measured frequency ranking of interview questions.
| Pass | Topics | Practice task |
|---|---|---|
| 1 | Filtering, nulls, aggregation | Count paid orders and compute a cancellation rate |
| 2 | Joins, EXISTS, NOT EXISTS |
Customers without orders; posts with heart reactions |
| 3 | CTEs, ranking, deduplication | Latest order and top three distinct salaries per department |
| 4 | Frames, LAG, LEAD |
Running revenue, trailing average, month-over-month change |
| 5 | Dates and gaps | Three-day streaks, sessions, next-day retention |
| 6 | Mixed patterns | Missing-date reports, all-products buyers, overlapping intervals |
Solve these without looking at the examples
- Return customers with at least two paid orders in January 2026.
- Return customers who placed orders but never a paid order.
- Return all posts with at least one heart reaction without duplicating posts.
- Return the second-highest distinct salary, including null if it does not exist.
- Return exactly up to two employees per department, breaking salary ties by employee ID.
- Return all employees in the two highest distinct salary levels per department.
- Return the latest order per customer.
- Compute the sum of the previous three orders, excluding the current order.
- Compute the sum of the current order and next two orders.
- Compute seven-calendar-date revenue when dates can be missing.
- Return every active streak of at least three days, including its start and end.
- Start a new session after an inactivity gap greater than 30 minutes.
- Compute the proportion of users active the day after their first observed activity.
- Return customers who purchased every required product.
- Return every student-subject pair, including pairs with zero attendances.
Answer hints
| Task | Key idea |
|---|---|
| 1 | Timestamp bounds + paid filter + GROUP BY + HAVING COUNT(*) >= 2 |
| 2 | One EXISTS for any order plus one NOT EXISTS for paid orders |
| 3 | Correlated EXISTS |
| 4 | MAX(salary) below the overall maximum |
| 5 | ROW_NUMBER ordered by salary descending and employee ID |
| 6 | DENSE_RANK ordered by salary only |
| 7 | ROW_NUMBER per customer ordered by timestamp descending and ID |
| 8 | ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING |
| 9 | ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING |
| 10 | Date RANGE from six days preceding through current date |
| 11 | Deduplicate days, form islands, aggregate each island |
| 12 | LAG, flag a gap, cumulative sum of flags |
| 13 | First date per user + next-day existence; complete observation window |
| 14 | No required product is missing: double NOT EXISTS |
| 15 | CROSS JOIN the expected pairs, then LEFT JOIN attendance |
Five fast self-checks
Does BETWEEN 1 AND 3 include 3? Yes.
Does 2 PRECEDING AND CURRENT ROW mean two rows total? No, up to three.
Does LAG mean yesterday? No, the previous row in the specified order.
Does EXISTS deduplicate the outer table? No. It avoids multiplying an outer row by its number of inner matches.
Is a CTE always materialized? No. It depends on the engine, query, and applicable optimization rules.
Platform practice
These are places to apply the patterns, not claims about exact employer interview frequency:
- LeetCode SQL 50: a structured set of SQL exercises. Look for joins, aggregation, subqueries, and ranking problems.
- DataLemur SQL questions: use the question catalog for analytical exercises such as rolling averages and rates.
- LeetCode Students and Examinations: practice constructing expected pairs and preserving zero counts.
When practicing, write down the grain and one edge case before writing the query. After solving, alter the input: add a duplicate, a null, a tie, a missing date, and an entity with no related records.