Relational modeling, joins, aggregation and window functions

You can now

Design a normalized schema with primary, foreign, unique and check constraints, and state which anomaly each normalization move fixes.
Predict row counts and duplicate behavior of multi-join queries before executing, including LEFT JOIN with WHERE and junction fan-out.
Write aggregation with HAVING, CTEs and window functions, and distinguish window row identity from GROUP BY collapsing.

Covers

Tables, keys, constraints and normalized relationships
Joins, aggregation, CTEs and window functions

Study, in this order

10 steps

The Relational Model
AlgoMaster SQL - selected route

NULL: The Missing Value Problem (NULL Comparisons, Three-Valued Logic, NULL in Practice); Relationships Between Tables (one-to-many, many-to-many, self-referencing)

Done
Keys and Constraints
AlgoMaster SQL - selected route

Primary Keys; Natural vs Surrogate Keys; Foreign Keys with ON DELETE; Composite Keys; Column Constraints (UNIQUE, NOT NULL, CHECK, DEFAULT)

Done
Normalization
AlgoMaster SQL - selected route

The Problem: Data Anomalies (update, insert, delete); First, Second and Third Normal Form with The Practical Test for 3NF; Boyce-Codd Normal Form; The Quick Test

Done
Entity-Relationship Modeling
AlgoMaster SQL - selected route

Cardinality: Many-to-Many (Extra Attributes on Junction Tables, Querying Through Junction Tables); From ER Diagram to Schema, Steps 1 to 5 and Common Translation Mistakes

Done
Introduction to Joins
AlgoMaster SQL - selected route

Join Types Overview; Choosing the Right Join Type; Joining More Than Two Tables

Done
`LEFT JOIN`
AlgoMaster SQL - selected route

Finding Unmatched Rows; ON vs WHERE: The Critical Difference; Common Patterns: Counting with Zeros and Default Values with COALESCE

Done
`GROUP BY` and `HAVING`
AlgoMaster SQL - selected route

The SELECT Rule with MySQL's Relaxed GROUP BY; HAVING: Filtering Groups; WHERE vs HAVING; SQL Execution Order with GROUP BY and HAVING; GROUP BY with Joins: What Gets Grouped

Done
Common Table Expressions
AlgoMaster SQL - selected route

CTE Syntax; Multiple CTEs; CTEs vs Subqueries vs Derived Tables; Materialization Behavior

Done
Introduction to Window Functions
AlgoMaster SQL - selected route

Window Functions vs GROUP BY; PARTITION BY; Combining PARTITION BY and ORDER BY; Default Frame Behavior

Done
`ROW_NUMBER`
AlgoMaster SQL - selected route

Deduplication with ROW_NUMBER; Top-\(N\) Per Group; ROW_NUMBER vs RANK vs DENSE_RANK

Done

Focus on

NULL never equals anything, not even NULL. Use IS NULL and IS NOT NULL; COUNT(column) skips nulls while COUNT(*) does not.
ON filters before the outer join adds its nulls, WHERE filters after. A WHERE condition on the right table silently converts LEFT JOIN to INNER JOIN.
GROUP BY grain decides counts: aggregate at the wrong grain and rows duplicate or vanish. HAVING filters groups after aggregation, WHERE filters rows before.
Window functions keep row identity while GROUP BY collapses rows. ROW_NUMBER with PARTITION BY gives top-\(N\) per group without losing the row.

Reproduce closed-book

Closed-book, on artists, tracks and streams tables: return every track with its stream count including zeros, per-artist totals with HAVING, and the top track per artist with ROW_NUMBER. Predict each row count before running.

Traps

Selecting non-grouped columns under GROUP BY. PostgreSQL rejects them (SQLSTATE 42803) unless the table's primary key is grouped, while SQLite and relaxed-mode MySQL return a value from some row of the group; list exactly the grain.
A surrogate primary key preventing logical duplicates. It does not; only a UNIQUE constraint on the natural key does.
COUNT(*) when you mean COUNT(column). The first counts null-extended outer-join rows, the second skips the nulls.

Practice

AlgoMaster Exercise - `LEFT JOIN`

Exercise 1: every track title with stream count, zeros included, ordered by stream_count then title

Predict the zero-count rows before running.

AlgoMaster Exercise - `GROUP BY` and `HAVING`

Exercise 2: one row per album_id with track_count and total_seconds

Add a HAVING variant that keeps only albums with more than two tracks.

Self-test

Tracks LEFT JOIN streams with WHERE streams.played_at > X. Which tracks survive?
5 artists and 12 tracks, each track with one artist. How many rows does the inner join return, and what changes with a junction table?
When do you use HAVING instead of WHERE?
How does ROW_NUMBER top-1 per group differ from GROUP BY with max?

Leads to

4.5 Transactions, isolation anomalies and ORM boundaries Guard the new schema with transactions and a retry policy.
4.6 Indexes, selectivity and query plans Diagnose the slow joins with plans and selectivity.

Comes after

4.2 JSON, JSONL, CSV, columnar formats and bounded ingestionLoad the normalized records into constrained tables.
4.3 Schema versions, compatibility and migrationsApply constraints and normalization to the versioned schema.

Resources for this unit

AlgoMaster SQL - selected route
AlgoMasterrequiredcourse
Study SQL Foundations, Querying Data, Joins, Subqueries and CTEs, Window Functions, DDL, DML and Views, Performance Optimization, Transactions and Concurrency, and Data Modeling. Stop when the service queries and transaction tests pass.

Also in this section

Official docs typed-service build path
Python typing, Pydantic, FastAPI and PostgreSQLprimarycourse
Build one record service in this order: structural protocols and generics; Pydantic models, validators, strict mode, serialization and settings; FastAPI request bodies, dependencies, errors and tests; then PostgreSQL transactions and EXPLAIN. Replay valid, malformed, duplicate and previous-schema requests through CLI and HTTP.
PostgreSQL transactions and EXPLAIN
PostgreSQLrequiredreference
Use the transaction tutorial and EXPLAIN guide to demonstrate commit, rollback, one isolation anomaly or prevented anomaly, and a measured plan improvement on the database actually used.
FastAPI tutorial
FastAPIsupportimplementation
Use Request Body, Dependencies, Handling Errors and Testing to expose the same service layer through HTTP. Keep framework models outside the domain core and preserve the same error semantics as the CLI.
AlgoMaster Low-Level Design - one rehearsal
AlgoMastersupportpractice
Use one fitting design prompt to rehearse interface contracts, dependency direction and failure ownership. Treat the review as feedback, then verify behavior with your own tests.
Stas' Python Cookbook - data and interfaces
Stas Bekmansupportreference
Use Serialization and Deserialization plus Environment and Arguments for focused implementation help. Compare JSON, JSONL, CSV and compressed data, and never use pickle for untrusted interchange.
AlgoMaster Python - interfaces, generics and data files
AlgoMastersupportcourse
Use Interfaces and Protocols plus Generic Types for structural typing, and Working with JSON plus Working with CSV for JSONL, error handling, newline and DictReader behavior. Lesson quizzes check recall; implementation is verified with your own scripts.
SQLAlchemy 2.1 documentation - select, Session and eager loading
SQLAlchemyreferencereference
Use the select() tutorial for 2.x query style, Session basics for the transaction boundary and identity map, and relationship loading for lazy versus selectinload versus joinedload plus raiseload. Use select() style and Session.execute or Session.scalars only, never the legacy Query API.
Alembic tutorial - migration scripts and downgrading
SQLAlchemysupportreference
Use Create a Migration Script, Running our First and Second Migration, Getting Information and Downgrading to run a paired upgrade and downgrade round trip on a scratch database.
PyArrow Parquet read and write docs
Apache Arrowsupportreference
Use Reading and Writing Single Files plus Table from_pydict and from_pylist to convert normalized records to Parquet and test types, nulls, column projection and schema.
Stripe idempotent requests
Stripesupportreference
Use the Idempotency-Key header contract, stored-response replay, parameter-mismatch errors and POST-only scope to implement keyed replay for one endpoint.