SQL module
Foundations · Module 04
SQL
Query Postgres like a data engineer: joins, aggregates, CTEs, and window functions. Practice only on your machine.
Beginner16 lessons~9 hours plus a demo
SQL is the daily language of warehouses and analytics databases. You already loaded retail orders with Python; this module teaches you to ask questions of that data with Postgres and psql.
You will stand up a local Postgres, seed stores/products/orders, then work through filters, joins, CTEs, and window functions. The site never runs SQL for you.
Start here
Finish Docker Getting Started so Compose works. Python is helpful context but not required — SQL Getting Started loads the retail CSVs itself. We create~/dcubes/sql-lab.
On-ramp
ReadyYour SQL practice environment
Start Postgres with Compose, install psql, and seed the retail CSVs used in every SQL lesson.
45 min
Lesson 1
ReadyWhy SQL for data work
Where SQL shows up in DE and analytics, and what this module will teach.
20 min
Lesson 2
Readypsql and the catalog
Use psql meta-commands to explore tables, columns, and connection settings.
25 min
Lesson 3
ReadySELECT and WHERE
Project columns, filter rows, sort, and limit — the core of every warehouse query.
30 min
Lesson 4
ReadyTypes and casts
Work with numeric, text, and timestamptz columns and cast safely in filters.
30 min
Lesson 5
ReadyAggregates
COUNT, SUM, AVG with GROUP BY and HAVING — metrics from row-level facts.
35 min
Lesson 6
ReadyJoins
Combine tables with INNER and LEFT JOIN and understand join keys.
35 min
Lesson 7
ReadyJoin patterns for facts
Multi-table retail queries, COALESCE, and filtering safely with outer joins.
35 min
Lesson 8
ReadySubqueries
Scalar subqueries, IN/EXISTS, and when a join is clearer.
35 min
Lesson 9
ReadyCTEs
Build readable query pipelines with WITH clauses and multiple common table expressions.
35 min
Lesson 10
ReadyWindow basics
OVER, PARTITION BY, and ORDER BY — row_number and rank without collapsing rows.
40 min
Lesson 11
ReadyWindow analytics
Running totals, LAG/LEAD, and moving comparisons for time-aware retail metrics.
40 min
Lesson 12
ReadyCASE and set ops
Conditional columns with CASE, and combine result sets with UNION ALL.
30 min
Lesson 13
ReadyDDL and load
Create tables, constraints, and reload CSVs idempotently for local labs.
35 min
Lesson 14
ReadyViews and EXPLAIN
Save a query as a view and read a simple EXPLAIN plan without panicking.
30 min
Lesson 15
ReadyQuery habits for DE
Grain checks, SELECT discipline, and validation queries you run after every load.
30 min