Learning SQL from scratch in 6 weeks

Week by week

1. Week 1Setup and the shape of data

   • Install PostgreSQL locally, or use a free hosted instance. Avoid browser-only sandboxes — they hide the connection step you will need later.
   • Load one real dataset with at least four related tables and a few hundred thousand rows.
   • SELECT, WHERE, ORDER BY, LIMIT. Twenty questions you make up yourself about your dataset.
   • Learn to read the schema: primary keys, foreign keys, and which columns are nullable.

   By the end of the week: A working local database and twenty answered questions about your own dataset.

2. Week 2Joins, properly

   • INNER and LEFT joins, and the habit of predicting the row count before you run the query.
   • What happens to a LEFT join when you put a condition on the right table in WHERE instead of ON — run it both ways and see.
   • Joining three and four tables. Aliasing consistently so the query is readable a week later.
   • Self-joins: employees and managers, or any parent-child relationship in your data.

   By the end of the week: You can predict a join's row count before running it and are usually right.

3. Week 3Aggregation and grouping

   • COUNT, SUM, AVG and the trap of COUNT(column) versus COUNT(*) when nulls are present.
   • GROUP BY with multiple columns; HAVING versus WHERE.
   • Aggregating after a join without double-counting — the single most common bug in analyst SQL.
   • CASE inside an aggregate to produce conditional counts in one pass.

   By the end of the week: A query that produces a monthly summary table from raw rows, with no double-counting.

4. Week 4Subqueries and CTEs

   • Subqueries in SELECT, FROM, and WHERE, and when each is appropriate.
   • Correlated subqueries, and rewriting one as a join to see the performance difference.
   • Common table expressions: break one long unreadable query into named steps.
   • EXISTS versus IN, particularly when nulls are involved.

   By the end of the week: You refactor an earlier query of yours into CTEs and it becomes readable.

5. Week 5Window functions

   • ROW_NUMBER, RANK, DENSE_RANK and the difference that shows up only on ties.
   • PARTITION BY: running totals and per-group rankings.
   • LAG and LEAD for period-over-period comparisons.
   • The classic interview question: find the second highest value per group, three different ways.

   By the end of the week: A month-over-month growth query written with window functions, no self-joins.

6. Week 6Reading query plans and interview practice

   • EXPLAIN on your slowest query. Learn to spot a sequential scan where an index would help.
   • Add an index, re-run EXPLAIN, and observe what changed.
   • Work through 25 interview-style SQL questions under time pressure.
   • Write up three queries you are proud of, with a short note on what each answers — this becomes portfolio material.

   By the end of the week: Three documented queries you can show an interviewer, and a rough sense of why a query is slow.

Where people lose time on this

• —Watching tutorials instead of writing queries. SQL is almost entirely a doing skill; passive hours barely count.
• —Switching datasets every week. Familiarity with the data is what lets you notice when a result is wrong.
• —Skipping window functions because they look advanced. They come up in most analyst interviews and are a fortnight's work at most.
• —Never checking whether a result is plausible. A query that runs is not a query that is correct, and nothing teaches this except being burned once.

Common questions