Unit 1

First queries

Ask a database a question and get a table back.

  1. 01Your first SELECTPull columns out of a table.Start
  2. 02Filter with WHEREKeep only the rows you care about.Open
  3. 03Sort and limitPut rows in order and keep the top few.Open

Unit 2

Sharper filters

Combine conditions, match patterns, and handle missing data.

  1. 04AND, OR and INCombine conditions without surprises.Open
  2. 05Patterns and NULLMatch text and find what's missing.Open
  3. 06Shape your resultsRename, calculate, de-duplicate and label.Open

Unit 3

Counting things

Summarise thousands of rows into a single answer.

Pro
  1. Count, sum, averageCollapse rows into one number.Pro
  2. GROUP BYOne summary row per group.Pro
  3. HAVINGFilter groups, not rows.Pro

Unit 4

Joining tables

Connect customers, orders and menu items at Rocket Pizza.

Pro
  1. Your first JOINMatch rows across two tables.Pro
  2. LEFT JOINKeep rows that have no match.Pro
  3. Joins that add upRevenue, totals and top customers.Pro

Unit 5

Combining results

Stack results with UNION, and join tables to themselves.

Pro
  1. UNION, INTERSECT, EXCEPTStack, overlap and subtract whole results.Pro
  2. Self-joinsJoin a table to itself: managers, pairs and neighbours.Pro
  3. CROSS JOINEvery combination of two lists.Pro

Unit 6

Queries inside queries

Subqueries and CTEs: break big questions into small ones.

Pro
  1. SubqueriesUse one query's answer inside another.Pro
  2. WITH: name your stepsCommon table expressions keep big queries readable.Pro

Unit 7

Window functions

Rankings and running totals — the interview favourite.

Pro
  1. Ranking with OVERNumber and rank rows without collapsing them.Pro
  2. Running totalsCumulative sums and looking back a row.Pro

Unit 8

Changing data

Insert, update and delete rows, without wrecking the table.

Pro
  1. Adding rowsINSERT new data, and get it back with RETURNING.Pro
  2. UPDATE and DELETEChange and remove rows, with WHERE as the safety catch.Pro
  3. UpsertsInsert or update in one statement with ON CONFLICT.Pro

Unit 9

Text and dates

Clean up text and do date maths, where PostgreSQL and SQLite differ most.

Pro
  1. Text functionsUPPER, LENGTH, || and friends.Pro
  2. Working with datesFilter by date and pull out years and months.Pro
  3. Date mathsAdd days and measure gaps between dates.Pro

Unit 10

Cleaning messy data

Fix blanks, stray spaces, text numbers and duplicates in a real-world import.

Pro
  1. Empty, NULL and defaultsNULLIF and COALESCE for the two kinds of missing.Pro
  2. Tidy text and numbersTrim, fix case and turn text into numbers.Pro
  3. Finding duplicatesSpot repeats and keep only the latest row.Pro

Unit 11

Designing tables

Create tables, add constraints, change them safely and build views.

Pro
  1. CREATE TABLEColumns, types, keys and defaults.Pro
  2. ALTER TABLE and viewsChange a table, and save a query as a view.Pro
  3. Keys and relationshipsForeign keys and storing each fact once.Pro

Unit 12

Recursive queries

WITH RECURSIVE for sequences, calendars and org charts.

Pro
  1. WITH RECURSIVEQueries that build on their own results.Pro
  2. Walking a hierarchyOrg charts: everyone below, everyone above.Pro

Unit 13

Advanced windows

Moving averages, shares of total, buckets and subtotals.

Pro
  1. Frames and sharesMoving averages, % of total, FIRST_VALUE.Pro
  2. Buckets and subtotalsNTILE, PERCENT_RANK and ROLLUP.Pro

Unit 14

Making queries fast

Indexes, query plans, and filters that can use an index.

Pro
  1. Indexes and EXPLAINCreate indexes and read query plans.Pro
  2. Index-friendly filtersKeep the column bare so an index can help.Pro

Unit 15

Analytics patterns

The capstone: product metrics on Streamly, a growing music app.

Pro
  1. Active users and growthDAU, month-over-month growth and rolling averages.Pro
  2. Funnels and conversionWho signs up, listens and pays, by channel.Pro
  3. Cohorts and retentionDo new users come back? Retention curves and churn.Pro

Unit 16

Interview pack

Original interview-style problems that combine everything. The clock is running.

Pro
  1. Warm-up roundFour easy problems to get your hands moving.Pro
  2. Core roundMedium problems: dates, rankings and pivots.Pro
  3. Final roundHard problems: islands, medians and top-N with ties.Pro