Welcome to the SQL Masterclass! This workshop is designed to take you from writing your first SELECT statement to authoring advanced data analytics pipelines with Window Functions, CTEs, and PostgreSQL-specific optimizations.
Regional pricing is available for eligible learners, with discounts of up to 60% in supported regions. Start here: https://www.learnwithparam.com/ai-bootcamp
This course uses a real-world e-commerce dataset containing over 100,000 orders from Olist, a Brazilian marketplace.
You will be querying real customers, real products, and real transactions across 9 tables.
┌──────────────────────┐ ┌──────────────────────┐
│ CUSTOMERS │ │ SELLERS │
│ customer_id (PK) │ │ seller_id (PK) │
│ zip_code, city, state│ │ zip_code, city, state│
└──────────┬───────────┘ └──────────┬───────────┘
│ │
│ 1 1 │
│ │
│ * * │
┌──────────▼───────────┐ ┌──────────▼───────────┐
│ ORDERS │ 1 * │ ORDER ITEMS │
│ order_id (PK) ├─────────► order_id (FK) │
│ customer_id (FK) │ │ product_id (FK) │
│ status, timestamps │ │ seller_id (FK) │
└──────────┬───────────┘ │ price, freight_value │
│ └──────────┬───────────┘
│ │ │ * * │ ┌──────────▼───────────┐ ┌──────────▼───────────┐
│ ORDER PAYMENTS │ │ PRODUCTS │
│ order_id (FK) │ │ product_id (PK) │
│ type, value, status │ │ category, weight, cm │
└──────────────────────┘ └──────────────────────┘
This masterclass offers two ways to set up the practice database:
This is the recommended path. We use Docker to spin up a PostgreSQL 16 database specifically for this course, isolated on port 5439 so it never conflicts with your other local projects.
Prerequisites: Docker, Docker Compose, Python 3.
# 1. Enter the directorycd lwp-workshops/sql-masterclass
# 2. Prepare the environment (downloads data, sets up python dependencies)
make setup
# 3. Start the PostgreSQL database and Adminer UI (Background containers)
make up
# 4. Load the 100k+ rows of E-Commerce data into PostgreSQL
make init-db
# 5. Connect and start learning!# We provide Adminer, a lightweight web-based database explorer.# Go to: http://localhost:8080# System: PostgreSQL | Server: postgres | Username: postgres | Password: masterclass | Database: ecommerce# Or connect using any visual client (DBeaver, DataGrip) via:# Host: localhost | Port: 5439 | User: postgres | Pass: masterclass | DB: ecommerce# Or connect via CLI:
docker exec -it sql-masterclass-db psql -U postgres -d ecommerceIf you don't have Docker, you can use SQLite. Note that Chapters 13–15 require PostgreSQL specifically and will not run on SQLite.
# 1. Enter the directorycd lwp-workshops/sql-masterclass
# 2. Make sure you ran `make setup` first to get the data
make setup
# 3. Initialize the SQLite database
make init-sqlite
# 4. Start practicing!
sqlite3 ecommerce.dbWe have included an automated test suite. As you complete exercises across the 15 chapters, simply run:
make verifyThis will run every single Query your .sql files contain against your local database to proactively find syntax errors or table mismatches!
There are 15 .sql files in this directory. Each file contains explanations, heavily commented examples, exercises, and solutions.
If you prefer reading Markdown, the docs/ folder contains a beautifully formatted Step-by-Step Lesson Guide with Mermaid diagrams for every single chapter.
| Level | Chapter File | Topics Covered | Doc Guide |
|---|---|---|---|
| 🟢 Beginner | 01-select-basics.sql | SELECT, LIMIT, AS | Doc |
| 🟢 Beginner | 02-filtering-with-where.sql | =, >, <, AND, OR, IN(), BETWEEN | Doc |
| 🟢 Beginner | 03-sorting-and-distinct.sql | ORDER BY, ASC, DESC, DISTINCT | Doc |
| 🟢 Beginner | 04-aggregate-functions.sql | SUM(), COUNT(), AVG(), MIN(), MAX() | Doc |
| 🟢 Beginner | 05-group-by-and-having.sql | GROUP BY, HAVING | Doc |
| 🟡 Intermediate | 06-case-expressions.sql | CASE WHEN..., Pivot | Doc |
| 🟡 Intermediate | 07-joins.sql | INNER, LEFT, CROSS, Self Joins | Doc |
| 🟡 Intermediate | 08-subqueries-and-ctes.sql | Subqueries, WITH CTEs | Doc |
| 🟡 Intermediate | 09-set-operations.sql | UNION, INTERSECT, EXCEPT | Doc |
| 🔴 Advanced | 10-date-and-string-functions.sql | EXTRACT(), Date Math | Doc |
| 🔴 Advanced | 11-window-functions.sql | OVER(), ROW_NUMBER(), LAG() | Doc |
| 🔴 Advanced | 12-advanced-analytics.sql | Cohorts, RFM, Funnels, CLV | Doc |
| 🟣 PostgreSQL | 13-postgresql-data-types.sql | ::NUMERIC, Arrays, JSONB | Doc |
| 🟣 PostgreSQL | 14-postgresql-functions.sql | DATE_TRUNC, FILTER | Doc |
| 🟣 PostgreSQL | 15-views-indexes-performance.sql | VIEW, INDEX, EXPLAIN | Doc |
| 🟣 PostgreSQL | 16-schema-design-normalization.sql | 3NF vs Star Schema; Building Fact/Dimension tables. | Doc |
| 🟣 PostgreSQL | 17-advanced-indexing.sql | Composite, Partial (WHERE), and Expression indexes. | Doc |
| 🟣 PostgreSQL | 18-table-partitioning.sql | Declarative Date Partitioning for massive scalability. | Doc |
| 🟣 PostgreSQL | 19-transactions-and-locks.sql | ACID, BEGIN/COMMIT/ROLLBACK, and Row-Level Locks. | Doc |
| 🟣 PostgreSQL | 20-stored-procedures-triggers.sql | PL/pgSQL loops, custom functions, and Audit Triggers. | Doc |