JOINs Match rows across tables
Every join starts from one table and reaches across to another, matching rows on a key. The join type decides what happens to the rows that don't find a match.
Window Functions Same table, no collapsing
A window function calculates across a group of rows without merging them into one — every row keeps its place, it just gets a new column of context.
GROUP BY & HAVING Filter before vs. after grouping
WHERE filters individual rows before grouping happens. HAVING filters the grouped rows afterward — it's the only place you can filter on an aggregate.
CTEs & Subqueries Name a subquery, then use it
A CTE runs an inner query first, gives it a name, and lets the outer query treat it exactly like a real table — useful whenever a subquery gets too nested to read.
Common Patterns Dedupe · Top-N · Pivot
Three queries you'll write constantly, all leaning on the same trick: number or reshape the rows first, then filter or aggregate around that.