Conditional Aggregation — Solution & ExplanationProblems / Conditional Aggregation / EditorialApproachConditional aggregation pivots row-level categories into columns:

SUM(CASE WHEN txn_type = 'credit' THEN amount ELSE 0 END) — sums only credit rows.

SUM(CASE WHEN txn_type = 'debit' THEN amount ELSE 0 END) — sums only debit rows.

Both run in a single GROUP BY pass, avoiding self-joins or subqueries.

Key ConceptThis pattern is the manual equivalent of a PIVOT operation. It transforms categorical values in rows into separate columns, which is one of the most common real-world SQL patterns.Hints

SUM(CASE WHEN condition THEN value ELSE 0 END) lets you sum only matching rows.

In PySpark, F.when(condition, value).otherwise(0) is the equivalent of CASE WHEN.

Solve Conditional Aggregation yourself →