Practice by topic · 9 drills
Subqueries & CTEs
Break a hard question into steps with subqueries, WITH and recursive CTEs. Ordered easiest first.
Read the guide first: SQL CTEs (WITH clause) and recursive CTEsStart: Customers with a big order 0/9 solved
- 1. Customers with a big orderIN (subquery) · EXISTS · Joining TablesUp next
- 2. Days to first purchaseCTE · MIN · date math · CTEs & Reshaping DataPro
- 3. Month-over-month growthCTE + LAG · growth rate · CTEs & Reshaping DataPro
- 4. Active in every monthCOUNT(DISTINCT month) · relational division · CTEs & Reshaping DataPro
- 5. Org chart with recursive CTEWITH RECURSIVE · hierarchies · CTEs & Reshaping DataPro
- 6. Second-largest chargeScalar subquery · MAX below the MAX · Interview ClassicsPro
- 7. Customers by number of ordersAggregate of an aggregate · LEFT JOIN · COUNT(column) · Interview ClassicsPro
- 8. Bought oat milk, never coffee beansEXISTS · NOT EXISTS · per-customer anti-join · Interview ClassicsPro
- 9. Plan at time of purchaseAs-of join · JOIN LATERAL · Analytics PatternsPro