SQL Learning Roadmap — From Zero to Interview-Ready

This is a practical roadmap for learning SQL, written for people who want to actually use it — not just pass a quiz. It covers what to learn, in what order, and how to practise each concept before moving on.

SQL is one of the most learnable technical skills there is. Most people stall not because it's hard, but because their practice has no stakes — they're writing queries that don't mean anything. This roadmap is designed to fix that.

Stage 1 — The basics (Week 1)

Start here even if you've written a few queries before. The goal at this stage is fluency with the fundamentals, not speed.

What to learn:

• SELECT — retrieve columns from a table
• WHERE — filter rows by condition
• ORDER BY — sort results
• LIMIT — control how many rows come back
• DISTINCT — remove duplicates

What good practice looks like at this stage: You should be writing queries against a real dataset with a real question to answer — not filling in blanks or picking from multiple choice. If you can't tell whether your answer is right or wrong from the output, the exercise isn't doing its job.

Practice resource: Query the Murder — Case 1. The first case starts with exactly these concepts, against a database of suspects and clues from an Agatha Christie novel. The schema is visible on screen, there's a real answer to find, and you get immediate feedback on every query.

Stage 2 — Joining tables (Week 2–3)

This is where most beginners stall. JOINs feel abstract until you have a reason to use them. The key insight: a JOIN lets you answer questions that require information from more than one table at the same time.

What to learn:

• INNER JOIN — rows that exist in both tables
• LEFT JOIN — all rows from the left table, matched where possible
• Joining on a shared key (e.g. person_id)
• Multi-table joins (joining three or more tables)
• Table aliases to keep queries readable

The mental model that helps: think of each table as a spreadsheet. A JOIN is how you do a VLOOKUP — matching rows from one sheet to rows in another based on a shared column.

Practice resource: Cases 2–5 on Query the Murder introduce JOINs progressively. By Case 5 you're joining three tables to reconstruct a suspect's timeline.

Stage 3 — Aggregation (Week 3–4)

Aggregation is how you go from individual rows to summaries — counts, totals, averages. This is where SQL becomes genuinely powerful for data analysis.

What to learn:

• COUNT, SUM, AVG, MIN, MAX
• GROUP BY — group rows before aggregating
• HAVING — filter after aggregation (not the same as WHERE)
• Combining aggregation with JOINs

Common mistake to avoid: using WHERE when you need HAVING. WHERE filters before grouping; HAVING filters after. If your condition references an aggregated value (like COUNT(*) > 3), use HAVING.

That rule is much easier to remember once you know the order clauses actually run in — see SQL order of execution, which walks through why WHERE simply cannot see a group that doesn't exist yet.

Stage 4 — Subqueries and CTEs (Week 4–5)

Subqueries let you use the result of one query as input to another. CTEs (Common Table Expressions, written with WITH) are a cleaner way to do the same thing. Both are essential for complex analytical questions.

What to learn:

• Subqueries in WHERE (e.g. find people whose salary is above the average)
• Subqueries in FROM (derived tables)
• WITH cte_name AS (...) — CTEs for readable multi-step logic
• When to use a CTE vs a subquery (hint: if you'd use the same subquery twice, use a CTE)

Stage 5 — Window functions (Week 6+)

Window functions are the last major concept and the one most likely to come up in a data analyst or data engineer interview. They let you perform calculations across a set of rows related to the current row — without collapsing results like GROUP BY does.

What to learn:

• ROW_NUMBER(), RANK(), DENSE_RANK()
• OVER (PARTITION BY ... ORDER BY ...)
• LAG() and LEAD() — compare a row to the previous or next row
• Running totals with SUM() OVER

Window functions appear in almost every data analyst interview at a mid-level or above. They're worth understanding properly, not just memorising syntax.

How long does this take?

With consistent daily practice (30–45 minutes), most people reach a solid intermediate level — comfortable with JOINs, aggregation, and subqueries — in 4–6 weeks. Window functions typically add another 2–3 weeks. The key variable is whether your practice is active (writing real queries against real data) or passive (watching videos).

Free tools for each stage

• Stages 1–4: Query the Murder — 18 murder mystery cases that cover everything from SELECT through CTEs. Browser-based, no account needed.
• All stages (reference): W3Schools SQL — quick syntax lookup, not great for practice but good as a reference.
• Intermediate–advanced: DataLemur — real interview questions from tech companies, good once you're past the basics.
• Advanced practice: LeetCode SQL — harder problems, useful for interview prep.

A note on dialects

SQL has several dialects — PostgreSQL, MySQL, SQLite, SQL Server, BigQuery. The core concepts (SELECT, JOIN, GROUP BY, subqueries) are identical across all of them. The differences are mostly in function names and a few syntax details. Learn the concepts first; dialect differences are easy to pick up once you know what you're doing.

Query the Murder uses SQLite, which is the most portable dialect and a sensible place to learn.