18. All Combinations with CUBE

Difficulty: Hard · Topics: Pivot, Unpivot & Rollup, Aggregations, Conditional Logic

Build an OLAP-style summary of orders across channel and category: every combination, the total per channel, the total per category and the overall total.

Return channel, category, orders (row count) and revenue (sum). In rows that total over a dimension, show 'ALL' in that column. A category that is genuinely missing in the data must stay null.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

orders

channel	category	revenue
web	books	20
web	toys	35
store	books	15
web	books	10

Expected output

channel	category	orders	revenue
web	books	2	30
web	toys	1	35
store	books	1	15
web	ALL	3	65
store	ALL	1	15
ALL	books	3	45
ALL	toys	1	35
ALL	ALL	4	80

Hints

Hint 1

df.cube("channel", "category") aggregates over all four grouping sets.

Hint 2

F.grouping_id() packs the grouping bits into one number: here 0 = both columns present, 1 = category rolled up, 2 = channel rolled up, 3 = grand total.

Hint 3

Replace a column with F.lit("ALL") only when its bit is set; don't use fillna, which would also overwrite real nulls.

PySpark functions you'll practise

agg
sum
count
cube
grouping_id
when / otherwise

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems