21. Conditional Aggregation

Difficulty: Medium · Topics: Aggregations, Conditional Logic

From the events table compute, per user_id, how many views, clicks and purchases they made, plus conversion = purchases ÷ views, rounded to 2 decimals.

A user with no views has a null conversion. Return user_id, views, clicks, purchases, conversion.

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

Sample data

events

user_id	event_type
1	view
1	view
1	click
1	purchase
2	view
2	click

Expected output

user_id	views	clicks	purchases	conversion
1	2	1	1	0.5
2	1	1	0	0

Hints

Hint 1

One groupBy with several F.sum(F.when(condition, 1).otherwise(0)) columns counts each event type in a single pass.

Hint 2

Division by zero yields null in Spark (with ANSI mode off), which is what we want.

Hint 3

F.round(x, 2) for the ratio.

PySpark functions you'll practise

• groupBy
• agg
• sum
• when / otherwise

Related problems

• Conversion Funnel · Hard · Aggregations
• Subtotals with ROLLUP · Hard · Pivot, Unpivot & Rollup
• All Combinations with CUBE · Hard · Pivot, Unpivot & Rollup
• Pivot with Several Aggregations · Hard · Pivot, Unpivot & Rollup
• Average Salary by Department · Easy · Aggregations

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