Pivot Average Order Size — Solution & ExplanationProblems / Pivot Average Order Size / EditorialApproach

Conditional AVG per column. AVG(CASE WHEN quarter = X THEN amount END) — the CASE routes only that quarter's amounts into the aggregate; everything else becomes NULL, which AVG skips.

Let NULL mean absent. The region with no Q3 orders gets AVG over nothing = NULL. Leaving it NULL is correct reporting; coercing to 0 fabricates a data point.

Round, order.

Key InsightThe ELSE 0 habit from SUM/COUNT pivots does not transfer to AVG — zeros are invisible to SUM but are *data* to AVG (they change the denominator). The general rule: in conditional aggregation, the ELSE branch must be the aggregate's identity element — 0 for SUM, but *NULL (absence)* for AVG, MIN, and MAX. Getting this wrong doesn't error; it just quietly reports averages diluted by phantom zeros, which is why this bug ships to production so often.Hints

For SUM pivots, ELSE 0 is harmless — adding zero changes nothing. For AVG it's catastrophic: every other quarter's orders enter the average as zeros and drag it down.

CASE with no ELSE returns NULL for non-matching rows, and AVG ignores NULLs entirely — so AVG(CASE WHEN quarter='Q1' THEN amount END) averages exactly the Q1 orders.

The empty cell falls out correctly for free: AVG over zero non-NULL values is NULL. Don't COALESCE it to 0 — 'no orders' and 'average order of $0' are different facts.

In PySpark, F.when(...) without .otherwise(...) is the same no-ELSE CASE.

Solve Pivot Average Order Size yourself →