Top Category per Region (No Windows) — Solution & ExplanationProblems / Top Category per Region (No Windows) / EditorialApproach

CTE 1 — region_category: SUM(revenue) GROUP BY region, category collapses raw sales into one total per pair.

CTE 2 — region_max: MAX(total_revenue) GROUP BY region over CTE 1 finds each region's winning total.

Join back: region_category JOIN region_max ON same region AND total_revenue = max_revenue. Every category whose total equals the regional max survives — North's Electronics and Furniture both total 400.00, so North contributes two rows.

Key InsightGroupby-max plus join-back is the classic argmax idiom from before window functions existed, and it is still worth knowing: it is the natural shape in engines and tools without windows, and its tie behavior is explicit — the equality join keeps all argmax rows, whereas ROW_NUMBER() = 1 would silently pick a single winner. (RANK() = 1 matches this query's semantics; comparing the two is a good self-check.)Hints

Two aggregations: SUM(revenue) per (region, category), then MAX of those sums per region.

Join the two aggregates on region and keep the rows where the category total equals the region max — an equality join preserves ties for free.

One region has two categories tied at the top: both rows must appear. ROW_NUMBER() would silently drop one; the join-back keeps both. PySpark is the same shape: two groupBys and a join.

Solve Top Category per Region (No Windows) yourself →