Two-Dimensional Crosstab — Solution & ExplanationProblems / Two-Dimensional Crosstab / EditorialApproachA crosstab (contingency table) is a pivot on counts: rows are one categorical dimension (plan), columns are the other (billing_period).

GROUP BY plan — one output row per plan.

COUNT(CASE WHEN billing_period = 'monthly' THEN 1 END) counts only monthly rows. With no ELSE, non-matching rows yield NULL, and COUNT ignores NULLs — so a plan with no annual subscriptions gets 0, not NULL, because COUNT over an all-NULL group is 0.

COUNT(*) gives the row total; no CASE needed.

In PySpark: F.count(F.when(F.col("billing_period") == "monthly", True)) per period and F.count(F.lit(1)) for the total.Key InsightSUM(CASE ... THEN amount END) pivots *values*; COUNT(CASE ... THEN 1 END) pivots *frequencies* — and COUNT's ignore-NULLs semantics is exactly what turns "no matching rows" into the 0 a crosstab requires (SUM with no ELSE would give NULL instead). The starter plan, which has only monthly subscriptions, is the proof in this dataset: its annual_count must be 0.Hints

This is conditional aggregation: GROUP BY plan, and inside each aggregate a CASE picks out one billing period.

COUNT(CASE WHEN billing_period = 'monthly' THEN 1 END) counts only matching rows — and returns 0 (not NULL) when nothing matches, because COUNT counts non-NULL inputs and never returns NULL over a group.

The total column needs no CASE at all: COUNT(*) per group. In PySpark use F.count(F.when(...)) per period and F.count(F.lit(1)) for the total.

Solve Two-Dimensional Crosstab yourself →