SQL & PostgreSQL learning roadmap.
Work through joins, transactions, query plans, and relational correctness. Work through 6 modules, then use the connected questions to check what you can explain without the notes.
- STEP 1
SQL & joins
- WHERE vs HAVING: what is the difference? · 2 min
- What is the difference between the join types? · 2 min
- Why does my join return duplicate rows? · 2 min
- Why does a LEFT JOIN behave like an INNER JOIN? · 2 min
- How should I compare NULL values? · 2 min
- Subquery vs EXISTS vs CTE? · 2 min
- When should I use a recursive CTE or set operation? · 2 min
Checkpoint: explain the ideas above with an example and a limitation before continuing.
- STEP 2
Window functions
- GROUP BY vs a window function? · 2 min
- ROW_NUMBER vs RANK vs DENSE_RANK? · 2 min
- What are LAG, LEAD and running totals? · 2 min
Checkpoint: explain the ideas above with an example and a limitation before continuing.
- STEP 3
Data modeling
Checkpoint: explain the ideas above with an example and a limitation before continuing.
- STEP 4
Transactions & locking
- ACID & transaction correctness · 2 min
- What are isolation levels and read anomalies? · 2 min
- Optimistic vs pessimistic locking? · 2 min
- What is a deadlock, and how do I handle it? · 2 min
Checkpoint: explain the ideas above with an example and a limitation before continuing.
- STEP 5
Indexes & optimization
- Which indexes should I know? · 2 min
- EXPLAIN vs EXPLAIN ANALYZE? · 2 min
- Is an index scan always faster than a sequential scan? · 2 min
- What do VACUUM, ANALYZE and autovacuum do? · 2 min
Checkpoint: explain the ideas above with an example and a limitation before continuing.
- STEP 6
PostgreSQL & ORMs
- JSON vs JSONB, and views vs materialized views? · 2 min
- What are schemas, roles, sequences, functions and triggers? · 2 min
- What does an ORM do underneath? · 2 min
- How can I practice joins and window functions? · 2 min
Checkpoint: explain the ideas above with an example and a limitation before continuing.
Check your understanding
After the lessons, rehearse the 24 connected questions. Revisit the lesson when you cannot yet explain why the answer works.
Practice SQL & PostgreSQL Review the interview guide