The SQL the exam puts on screen

Lesson 1 of 4 in Programming Concepts on the Exam.

You will not be asked to write SQL. You will be shown some and asked what it returns, or why it is slow, or which of four statements produces the required result. Four constructs account for almost all of it.

Window functions. The distinction from GROUP BY is the whole point: a group-by returns one row per group, a window function returns every row with the aggregate attached. The form you will see most often is deduplication:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn FROM t) WHERE rn = 1

Read it as: group by id, order each group newest first, number them, keep the first of each. That is "the latest version of every record", which is exactly what you need over an append-only change feed.

Two details that change the answer. PARTITION BY is a shuffle key, so a window over a high-cardinality column is a full shuffle of the dataset and over a skewed one is a skew problem. And ROW_NUMBER, RANK and DENSE_RANK differ on ties — RANK leaves gaps, DENSE_RANK does not, ROW_NUMBER breaks ties arbitrarily — so picking the wrong one silently changes a deduplication result.

Common table expressions. WITH recent AS (...) names an intermediate result. The misconception worth correcting: a CTE is not a temporary table and is not guaranteed to be computed once. Engines may inline it into each reference, so a CTE referenced three times may scan its source three times. If you need it computed once, materialise it deliberately — CREATE TABLE AS SELECT in Athena, a temporary table in Redshift, .cache() in Spark.

Join types, read for their effect on row counts. An INNER JOIN can lose rows; a LEFT JOIN preserves the left side and introduces nulls; and a join on a non-unique key multiplies rows, which is the commonest cause of a number being inexplicably too high. If a stem reports a total that grew after a join was added, suspect the key's uniqueness before anything else.

GROUP BY with HAVING versus WHERE. WHERE filters rows before grouping; HAVING filters groups after. Putting an aggregate in WHERE is an error, and putting a row predicate in HAVING is legal and wasteful — you grouped data you were about to discard.

Four SQL shapes that appear in stems, what each one is for, and the cost it carries. The cost column is the half people do not read.
Shape	Returns	Use it for	What it costs
`GROUP BY customer_id`	one row per customer	an aggregate — a total, a count, a maximum	a shuffle on `customer_id`; skewed if one customer dominates
`ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC)`	every row, numbered within its id	deduplication, "latest version of each record", top-N per group	a shuffle on `id` and a sort within each partition — more expensive than a group-by
`LAG(value) OVER (PARTITION BY id ORDER BY ts)`	every row, with the previous row’s value attached	deltas, change detection, session gap calculation	the same shuffle and sort; the function itself is cheap
`WITH x AS (…) SELECT … FROM x JOIN x`	whatever the outer query asks for	readability — naming an intermediate step	possibly scanning the source once per reference, since a CTE may be inlined rather than materialised
`LEFT JOIN b ON a.k = b.k` where `b.k` is not unique	more rows than `a` had	nothing — this is almost always a defect	silently multiplying every total downstream; check key uniqueness before trusting a number
`WITH RECURSIVE …`	a walk over a hierarchy to arbitrary depth	org charts, bills of materials, category trees	an easy way to write a query that never terminates — a depth guard is not optional

Interactive sorting exercise: Each card is a requirement stated the way a stem states it. Sort it into the SQL construct that satisfies it — three of these look like a `GROUP BY` and are not.

A five-layer vertical stack read bottom-up, describing three approaches to selecting the latest version of each record from a change feed of 100 million rows covering 12 million distinct keys. Bottom layer, the input: 100 million change rows, 12 million distinct keys, roughly eight versions per key. Second layer, approach one, a window function with ROW_NUMBER partitioned by key and ordered by commit timestamp descending, then filtered to rank one: returns exactly 12 million rows, requires one shuffle on the key plus a sort within each partition, and is the standard form. Third layer, approach two, a group-by taking the maximum commit timestamp per key followed by a join back to the source on key and timestamp: returns 12 million rows if the timestamp is unique per key, and more than 12 million if two versions of a key share a timestamp, because the join then matches both; it requires a shuffle for the aggregate and a second shuffle for the join. Fourth layer, approach three, a self-join with a NOT EXISTS predicate asserting that no later row exists for the key: returns 12 million rows and is correct, and expresses the same work as a semi-join whose cost depends heavily on the engine and the statistics available. Top layer, emphasised, the conclusion: all three can be correct, the group-by-then-join form is the one that silently returns too many rows when the ordering column is not unique within a key, and that duplicate-timestamp case is the defect an exam item is built on.

The same deduplication, three ways. The row counts are what the exam asks about; the relative cost is what decides which one you would write. (illustrative — source: Amazon Redshift database developer guide — window functions)

Key terms: Window function, Common table expression (CTE), Query execution plan

Interactive checkpoint quiz (2 questions) — open this page in a browser to take it.