17. Subtotals with ROLLUP

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

Finance wants one report with three levels of totals from the sales table: revenue per region and product, a subtotal per region, and a grand total.

Return region, product, total (sum of amount) and level, which is 'detail', 'region subtotal' or 'grand total'.

Careful: some sales have no product recorded (product is null). Those are still 'detail' rows; a null alone must not be mistaken for a subtotal.

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

Sample data

sales

region	product	amount
North	Laptop	1200
North	Phone	800
North	Laptop	300
South	Phone	500
South	Tablet	450

Expected output

region	product	total	level
North	Laptop	1500	detail
North	Phone	800	detail
South	Phone	500	detail
South	Tablet	450	detail
North	null	2300	region subtotal
South	null	950	region subtotal
null	null	3250	grand total

Hints

Hint 1

df.rollup("region", "product") produces the (region, product), (region) and () groupings in one pass.

Hint 2

In subtotal rows the rolled-up columns are null. F.grouping("product") returns 1 only when the null comes from the rollup, not from the data.

Hint 3

Compute grouping() inside .agg(...), then build level with F.when.

PySpark functions you'll practise

• agg
• sum
• rollup
• grouping
• when / otherwise
• select

Related problems

• All Combinations with CUBE · Hard · Pivot, Unpivot & Rollup
• Conditional Aggregation · Medium · Aggregations
• Sessionize a Clickstream · Hard · Window Functions
• Conversion Funnel · Hard · Aggregations
• Categorize Orders · Easy · Conditional Logic

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