◀ Your progress Course contents

Course contents

Part 1

Reading Data

6 Modules

Everything you need to pull exactly the rows and columns you want out of a table: selecting, filtering, sorting, de-duplicating, the NULLs that break a naive filter, and the CASE expression that buckets rows into categories. No prior database experience assumed.

1

SELECT & FROM

Read a table, choose the columns you want, and meet the practice database you will be querying for the rest of the dojo.

SELECT FROM Schema
2

Filtering with WHERE

Cut a table down to the rows that matter, combine conditions without getting the logic wrong, and use IN, BETWEEN and LIKE.

WHERE AND / OR IN & LIKE
3

Sorting & Limiting

Control the order rows come back in, break ties on purpose, and take the top of a list — or a window out of the middle of it.

ORDER BY DESC LIMIT / OFFSET
4

DISTINCT, Aliases & Expressions

De-duplicate a column, compute new columns from existing ones, and name every output so the result reads for itself.

DISTINCT AS Expressions
5

NULL and Three-Valued Logic

The value that is not a value: why = NULL never matches, how NULL spreads through a calculation, and how to substitute it.

IS NULL COALESCE Unknown
6

CASE Expressions

SQL's if-statement. Bucket rows into named categories, get the branch order right, and never forget the ELSE.

CASE WHEN ELSE Bucketing
Part 2

Summarising

5 Modules

Turning rows into numbers: counting, totalling and averaging, one number per category, one row per month, and the handful of functions that reshape a value on its way out — including the division that quietly returns zero.

1

COUNT, SUM & AVG

The five functions that collapse many rows into one value, the two COUNTs that disagree about what counting means, and the empty result that hands back NULL rather than zero.

COUNT(*) vs COUNT(col) DISTINCT COALESCE
2

GROUP BY

One number per category instead of one for the table — plus the bare column in a SELECT list that Postgres refuses by name and the lenient databases answer from a row nobody chose.

Grouping Several columns Bare columns
3

HAVING & Filter Order

Filtering groups rather than rows, the order a query really runs in, and the one condition that is legal in either clause and gives a different answer in each.

HAVING Evaluation order WHERE vs HAVING
4

Working with Dates

Filtering and grouping by day, month and year on real DATE columns, measuring a gap in days, and the range filter that loses the afternoon of the last day.

date_trunc EXTRACT & INTERVAL Half-open ranges
5

Text & Number Functions

Cutting and gluing text, what a single NULL does to a concatenation, and the percentage that comes back as 0 for every dataset ever written.

substr & strpos Concatenation Integer division
Part 3

Combining

6 Modules

Bringing several tables together without losing or duplicating rows. The join that multiplies, the join that deletes, and the two mistakes that turn an outer join back into an inner one without saying so.

1

INNER JOIN

Lining two tables up on the value they share, why a missing condition returns 288 rows instead of an error, and the unmatched rows a join removes without mentioning it.

JOIN ... ON Table aliases Fan-out
2

LEFT JOIN & Missing Rows

Keeping the rows an inner join throws away, the two-line pattern for finding what is missing, and the WHERE clause that silently undoes the whole thing.

Anti-join ON vs WHERE COUNT the padding
3

Joining Three Tables

Chaining joins to reach a column two tables away, grouping by two dimensions at once, and the inner join downstream that silently cancels the outer join above it.

Join chains COUNT(DISTINCT) Row-count check
4

Self Joins & UNION

Joining a table to itself to follow a key that points back into it, the comparison that stops every pair appearing twice, and the UNION that quietly deletes rows you needed.

Self join UNION ALL INTERSECT & EXCEPT
5

Subqueries

The four places a SELECT can sit inside another statement, the subquery that sees the row around it, and the NOT IN that returns nothing at all once a single NULL appears.

Scalar & correlated EXISTS NOT IN and NULL
6

Common Table Expressions

Naming each step instead of nesting it, the step reused twice that a derived table would make you repeat, and the recursive form that walks a hierarchy of unknown depth.

WITH WITH RECURSIVE generate_series
Part 4

Analyst SQL

5 Modules

Ranking a row within its group, comparing it to its neighbours, running totals, funnels and retention cohorts — and, at the end, changing data rather than only reading it.

1

Window Functions

The aggregate that summarises without collapsing the rows it summarises, ranking inside a group, and the three ranking functions that only disagree once there is a tie.

OVER & PARTITION BY RANK vs ROW_NUMBER Top N per group
2

Running Totals, LAG & LEAD

Totals that accumulate down the rows, reaching back to the previous month and forward to the next, and the default frame that quietly adds every tied row at once.

Window frames ROWS vs RANGE LAG & LEAD
3

Funnels & Cohorts

Conversion funnels and the segment that changes the answer, retention grids, and the judgement about how wide a cohort has to be before its percentages mean anything.

COUNT FILTER Step conversion Retention grid
4

INSERT, UPDATE & DELETE

The statements that change data, the missing WHERE clause that changes every row in the table, the keys that refuse to orphan anything, and the transaction that lets you take it back.

RETURNING The missing WHERE Transactions
5

Capstone: Your First Week

No lesson and no new syntax — a mock analytics job instead. An undocumented database, five stakeholders asking for numbers in their own words, and fifteen queries between Monday and the board pack on Friday.

Case study Stakeholder requests Reconciling a report