datarekha
Section 5 chapters · 27 of 27 lessons

SQL

From SELECT to window functions, recursive CTEs, and the analytics patterns you'll actually be paid to write. Postgres-first with Snowflake/BigQuery/Redshift notes.

The SQL journey 0 / 27 completed
  1. Chapter 01

    Foundations

    6 lessons
  2. 01 SELECT basics The fundamentals of a SQL query — choosing columns, filtering rows, sorting — and the surprising order they actually run in. Beginner6 min
  3. 02 WHERE & filtering How to keep only the rows you care about — comparisons, ranges, lists, patterns, and the logic that combines them. Beginner6 min
  4. 03 ORDER BY, LIMIT, DISTINCT Sorting results, paging through them, and removing duplicates — the small clauses you touch in almost every query. Beginner5 min
  5. 04 Aggregates & GROUP BY Counting, summing, averaging — the math that turns rows into answers — plus the WHERE-versus-HAVING distinction every beginner gets wrong. Beginner7 min
  6. 05 CASE expressions SQL's if/else — conditional logic inside SELECT, ORDER BY, and aggregates. The Swiss-army knife you reach for constantly. Beginner5 min
  7. 06 NULLs done right Why col = NULL is silently wrong, how NULL behaves in aggregates and groups, and the handful of functions that tame it. Intermediate6 min
  8. Chapter 02

    Joins

    3 lessons
  9. 07 INNER JOIN Combining rows from two tables on a matching condition — the single most-used multi-table operation in SQL. Beginner6 min
  10. 08 LEFT, RIGHT, FULL When you need rows from one side even where the other side has no match — the joins that surface what's missing. Beginner6 min
  11. 09 Anti-joins Users who never ordered. Products that never sold. The three idioms for 'rows in A with no match in B' — and the NOT IN trap that bites everyone. Intermediate5 min
  12. Chapter 03

    Advanced Query Building

    5 lessons
  13. 10 Subqueries Queries inside queries — scalar, IN, EXISTS, and the correlated subquery every analyst eventually writes. Intermediate6 min
  14. 11 CTEs (WITH) WITH clauses turn nested SQL into readable, named steps — the single biggest readability win in modern SQL. Intermediate7 min
  15. 12 Recursive CTEs The SQL that handles hierarchies, sequences, and graph walks — the one feature that makes SQL Turing-complete. Advanced8 min
  16. 13 Window functions The SQL feature that separates juniors from seniors — running totals, rankings, lag/lead — all without grouping the rows away. Advanced10 min
  17. 14 Ranking functions ROW_NUMBER, RANK, DENSE_RANK, NTILE — the four ranking window functions, and the subtle tie behaviour that bites. Intermediate6 min
  18. Chapter 04

    Analytics Patterns

    4 lessons
  19. 15 Top-N per group The pattern in every analytics interview — top 3 products per category, latest order per user, best score per team — and the wrap-and-filter idiom… Intermediate6 min
  20. 16 Deduplication Duplicates in production data are the rule, not the exception. Two SQL patterns to remove them — and the judgement call that decides which row surv… Intermediate6 min
  21. 17 Cohort analysis The retention triangle every PM asks for — assign users to a signup cohort, measure their activity month by month, and pivot it into the analyst fl… Advanced8 min
  22. 18 Sessionization Turn an event stream into sessions — the gap-and-islands pattern every analyst eventually writes, built step by step with window functions. Advanced8 min
  23. Chapter 05

    Data Platforms & Pipelines

    9 lessons
  24. 19 OLTP vs OLAP Why you never point your dashboards at the production app database — the two database workload archetypes, the storage layout that drives the whole… Intermediate8 min
  25. 20 Dimensional Modeling How analytical tables are actually shaped — facts vs dimensions, declaring the grain, and why the star schema's denormalised dimensions beat a norm… Beginner9 min
  26. 21 Slowly Changing Dimensions When a customer moves or a product is recategorised, do you overwrite the past or remember it? SCD Type 1, 2, and 3 — and the surrogate-key trick t… Intermediate9 min
  27. 22 Warehouse, Lake & Lakehouse Three ways to store analytical data — and why the lakehouse is just a data lake plus a transaction layer. Schema-on-write vs read, the data swamp,… Beginner9 min
  28. 23 Columnar Storage & Parquet Why an analytical engine can sum a billion-row table while reading almost none of it. Row vs column layout, how Parquet's row groups and column chu… Intermediate9 min
  29. 24 ETL vs ELT Why the modern data stack moved the Transform step AFTER the Load — out of a separate engine and into cheap, elastic cloud-warehouse compute that a… Beginner8 min
  30. 25 Change Data Capture (CDC) How modern pipelines keep a warehouse fresh to the second — by streaming every insert, update, and delete out of a source database the instant it c… Intermediate9 min
  31. 26 Reverse ETL The warehouse has your best data — joined, modeled, scored — but it's trapped in dashboards. Reverse ETL syncs that data back OUT to the tools wher… Beginner7 min
  32. 27 Orchestration: Airflow & DAGs A real data platform is dozens of jobs with dependencies — extracts, transforms, loads, CDC sinks, reverse-ETL syncs. Why cron can't run them safel… Intermediate8 min
  33. End of section 0 / 27 complete

    Make it stick — pass every quiz.

    Each lesson has a short quiz at the bottom. Passing the quiz is what marks the lesson complete and counts toward your certificate.

    Section complete 27 / 27 lessons

    Nice work — you finished SQL.

    Certificates are earned per learning path, not per section. Here's where this section takes you:

FAQCommon questions

SQL — frequently asked questions

Straight answers to the questions people ask most about sql.

What's the difference between WHERE and HAVING?

`WHERE` filters individual rows before grouping; `HAVING` filters groups after `GROUP BY` has aggregated them. Use `WHERE` for row conditions and `HAVING` for conditions on aggregates like `COUNT(*) > 5`.

When should I use a window function instead of GROUP BY?

Use a window function when you need an aggregate alongside the original rows — a running total, a rank within each group, or each row's share of its group's total — without collapsing the rows. `GROUP BY` is for when you only want one summary row per group.

Read the lesson
What's the difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN keeps only rows that match in both tables; a LEFT JOIN keeps every row from the left table and fills NULLs where the right has no match. Use LEFT JOIN when you want to keep all records from your primary table even when related data is missing.

Why is my SQL query slow?

Usually it's missing indexes on join or filter columns, functions wrapped around indexed columns (which disable the index), or returning far more rows than needed. Read the query plan with EXPLAIN, index the columns you filter and join on, and avoid `SELECT *` on wide tables.

What is a CTE and when should I use one?

A CTE (Common Table Expression, the `WITH` clause) names a subquery so you can reference it like a temporary table. Use it to break a complex query into readable steps, or recursively to walk hierarchies like org charts. It mainly improves readability.

Skip to content