BLOG  /  SQL PATTERNS

SQL & QUERYING · 24 PATTERNS · 6 STAGES

Learn SQL, one pattern at a time

Each pattern is a focused lesson on writing queries that return exactly the rows you meant, from the foundations up to the deeper cuts. The path below runs in order, but you can start anywhere.

LEARN THE CONCEPTS. VALIDATE YOUR SKILLS.

Explore the SQL patterns, then take the free diagnostic to find your strengths, identify your gaps, and know exactly what to learn next.

No card required  ·  Save your progress

Take the free assessment

00

START HERE

Introduction to Databases and SQL

What a database is, how the relational model organises information, and what SQL lets you do: the ground floor before the patterns.

Read the intro →

01

FOUNDATIONS

P01–P03

Filter, order, and trust your first queries.

P01Filtering Rows & ConditionsThe filter that keeps unintended rowsIN THE DIAGNOSTICAND runs before OR. Parentheses define the logic you meant.P02NULL & UnknownNULL changes how comparisons workHandle missing values correctly with IS NULL, COALESCE, and NULLIF.P03Ordering & Top-NReturn the same top rows every timeUse ORDER BY, LIMIT, and tie-breakers to make Top-N queries stable.

↓ NEXT  SUMMARISING DATA

02

SUMMARISING DATA

P04–P06

Turn rows into totals that answer the right question.

P04Aggregation & GROUP BYThe GROUP BY that answers a different questionIN THE DIAGNOSTICOne extra grouping column splits a per-customer total into per-status rows.P05Conditional Logic & CASEThe CASE that can never return LargeList a broader WHEN first and the specific category is unreachable.P06Conditional AggregationThe COUNT that counts every rowCOUNT(CASE .. ELSE 0) counts non-NULL, so both 1 and 0 get counted.

↓ NEXT  COMBINING DATA

03

COMBINING DATA

P07–P10

Join tables without inventing or losing rows.

P07JoinsThe LEFT JOIN that quietly becomes an INNERA right-table filter in WHERE drops the unmatched rows you meant to keep.P08EXISTS & Anti-JoinsThe JOIN that answers a yes/no question with duplicatesAn INNER JOIN multiplies rows when you only needed to know a match exists.P09Set OperationsThe UNION that quietly drops a rowUNION removes duplicate rows; UNION ALL keeps every meaningful record.P10Subqueries & CTEsThe subquery that answers the wrong questionA missing correlation compares each row to the overall average, not its own group.

↓ NEXT  WORKING WITH REAL-WORLD DATA

04

WORKING WITH REAL-WORLD DATA

P11–P14

Survive dates, strings, and types as they actually arrive.

P11Dates & TimestampsThe month that loses its final afternoonBETWEEN on a timestamp column stops at the start of the last day and drops the rest.P12String FunctionsThe lookup that misses a value that looks identicalAn equality check fails on a stored value with hidden spaces or different capitalization.P13Data Types & CASTThe numeric filter that quietly compares textDigits stored as text sort like characters, so '90' can outrank '1000' until you CAST.P14JSON & Semi-structuredThe JSON number that compares as textExtracting a nested value gives you text, so '900' can outrank '1000' until you CAST.

↓ NEXT  ANALYTICAL & SEQUENCE

05

ANALYTICAL & SEQUENCE

P15–P19

Rank, compare, and read order across rows.

P15Window Functions & RankingThe rank that skips a number after a tieRANK() leaves a gap after ties, so the third distinct value lands at rank 4, not 3.P16Running Totals & Moving AveragesThe three rows mistaken for three daysA ROWS frame counts positions, not calendar time, so 2 PRECEDING can span a month.P17LAG, LEAD & Value FunctionsThe previous row from the wrong sequenceA LAG without PARTITION BY compares one customer's order against another's.P18Deduplication & Latest RowThe latest row that keeps changingA tied timestamp with no tie-breaker makes ROW_NUMBER pick a winner at random.P19Gaps, Islands & StreaksThe streak that secretly counts twiceTwo events on one day inflate an active-day streak unless you fix the grain first.

↓ NEXT  ADVANCED SQL

06

ADVANCED SQL

P20–P24

Reshape, recurse, and measure whole journeys.

P20Pivoting & ReshapingThe pivot that duplicates a customerAn extra GROUP BY column changes the grain, so one customer becomes several rows.P21Recursive CTEs & HierarchiesThe join that finds only immediate childrenA single self-join follows one level; a recursive CTE repeats it to every descendant.P22Funnels & ConversionThe funnel that counts the wrong orderCounting each stage independently credits users who did the steps out of sequence.P23Cohorts & RetentionThe retention rate above 100%Counting events instead of distinct users lets one active customer look like several.P24Sessionization & Event SequencesThe session that joins two usersA LAG without PARTITION BY measures the gap across users and merges their journeys.

↓ NEXT  END OF THE PATH

✓

END OF THE PATH

You've reached the deep cuts. Take the diagnostic to see which patterns you actually need.

Take the free assessment