Skip to content

SQL for interviews

Module 7 of the System Design Masterclass. Every lesson here is free to read. No account needed.

  • 9 lessons
  • 3 h 12 min of reading
  1. 42SQL basics: SELECT, WHERE, ORDER BY and LIMITRead a table, ask it a plain question, and get the rows back in the right order, with the traps of AND and OR, text dates, case, and whole-number division.22 minCore
  2. 43SQL values: NULL, COALESCE, CASE and datesHandle missing values and dates without silent wrong answers: what NULL really is, IS NULL, COALESCE, NULLIF, CASE, and the date functions of SQLite.21 minCore
  3. 44SQL joins: inner, left, anti-join and self-joinCombine tables with INNER and LEFT JOIN, keep or drop unmatched rows on purpose, find rows with no match, compare rows of one table with a self-join, and avoid the fan-out trap.21 minCore
  4. 45SQL groups: GROUP BY, HAVING and conditional countsSummarise rows per group with COUNT, SUM, AVG, MIN and MAX; filter groups with HAVING; count only what matches a rule; and find duplicates.18 minCore
  5. 46SQL queries inside queries: subqueries, EXISTS, CTEs and recursionPut one query inside another, test for rows that exist or do not exist, build a long query in named steps with WITH, stack results, and use recursion for days, numbers and trees.20 minCore
  6. 47SQL window functions: ranks, running totals, LAG and framesAdd a calculation over a group of rows without losing the rows: totals and shares beside each row, ranks, running totals, LAG and LEAD, and moving averages with frames.20 minCore
  7. 48SQL patterns that interviews repeatRecipes for the questions that come back in every SQL round: top N per group, the latest row, duplicates, medians, the price on a date, running balances, shares of a total and pairs.21 minDepth
  8. 49SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windowsRecipes for questions about days and order of events: login streaks and gaps, sessions, cohorts and retention, funnels, rolling windows, spikes, and year over year.24 minDepth
  9. 50Speaking in a SQL round: ask, check, then make it fastRun a SQL interview in a fixed order: ask questions, build and check the query step by step, explain it aloud, and answer the speed question with an index and a query plan.25 minCore

Want help while you learn?

Reading is free for everyone. A free account adds the AI tutor, quizzes, notes and your progress.

Sign up free to ask the tutor