20. Pivot with Several Aggregations
Difficulty: Hard · Topics: Pivot, Unpivot & Rollup, Aggregations, Null Handling
For each customer, report the number and value of completed and returned orders side by side.
Return customer, completed_orders, completed_amount, returned_orders, returned_amount. Use 0 where a customer has no orders of that status. Other statuses (e.g. 'cancelled') are ignored, but a customer who only has those still appears with zeros.
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
orders
| customer | status | amount |
|---|---|---|
| ana | completed | 50 |
| ana | completed | 30 |
| ana | returned | 30 |
| ben | completed | 20 |
| ben | cancelled | 99 |
Expected output
| customer | completed_orders | completed_amount | returned_orders | returned_amount |
|---|---|---|---|---|
| ana | 2 | 80 | 1 | 30 |
| ben | 1 | 20 | 0 | 0 |
Hints
Hint 1
groupBy("customer").pivot("status", ["completed", "returned"]): listing the values skips a scan and fixes the column order.Hint 2
With several aggregations, Spark names the columns<value>_<alias>.Hint 3
Missing combinations come back asnull; finish with fillna(0).PySpark functions you'll practise
- groupBy
- agg
- sum
- count
- pivot
- fillna
Related problems
- Monthly Retention by Cohort · Hard · Joins
- Pivot Quarterly Revenue · Medium · Pivot, Unpivot & Rollup
- All Combinations with CUBE · Hard · Pivot, Unpivot & Rollup
- Subtotals with ROLLUP · Hard · Pivot, Unpivot & Rollup
- Fill Missing Dates (Calendar Spine) · Hard · Joins
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