49. Conversion Funnel

Difficulty: Hard · Topics: Aggregations, Conditional Logic

The events table has user_id and step ('visit', 'signup', 'purchase'; users may repeat steps). A user counts at a step only if they also completed every earlier step.

Return a single row: visits, signups, purchases (user counts) and signup_rate and purchase_rate (percent of the previous step, 1 decimal; null if the previous step is 0).

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	step
1	visit
1	signup
1	purchase
2	visit
2	signup
3	visit
4	visit
4	visit

Expected output

visits	signups	purchases	signup_rate	purchase_rate
4	2	1	50	50

Hints

Hint 1

First reduce to one row per user with a 0/1 flag per step: F.max(F.when(step == "visit", 1).otherwise(0)).

Hint 2

A user reaches signup only if visit AND signup: multiply the flags.

Hint 3

Then sum the flags across users and compute the rates.

PySpark functions you'll practise

• groupBy
• agg
• sum
• max
• when / otherwise

Related problems

• Conditional Aggregation · Medium · Aggregations
• Subtotals with ROLLUP · Hard · Pivot, Unpivot & Rollup
• All Combinations with CUBE · Hard · Pivot, Unpivot & Rollup
• Pivot with Several Aggregations · Hard · Pivot, Unpivot & Rollup
• Consecutive Status Runs (Gaps and Islands) · Hard · Window Functions

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