Funnel Count: Compute Drop-Off at Each Step of a Conversion Funnel
Interview Experience
Problem
You have a list of user events (user_id, event_name, timestamp). A conversion funnel is defined as an ordered list of event names. A user "completes" step i of the funnel if they fired event i after completing step i-1 (events must occur in order but not necessarily consecutively). Compute the number of unique users who reached each step.
python
def funnel_count(
events: list[tuple[int, str, int]], # (user_id, event, timestamp)
funnel: list[str]
) -> list[int]:
"""Return list of user counts at each funnel step."""
pass
**Input**:
events = [(1,"view",1),(1,"click",2),(1,"purchase",3),
(2,"view",1),(2,"click",4),
(3,"view",2)]
funnel = ["view", "click", "purchase"]
**Output**: [3, 2, 1]
# All 3 users reached step 1 (view)
# Users 1,2 reached step 2 (click)
# Only user 1 reached step 3 (purchase)
Follow-ups
- How do you write this as a SQL query using self-joins or window functions?
- If the funnel must be completed within a time window (e.g., 7 days), how does your logic change?
- How would you compute conversion rates and visualize the drop-off percentages?
- Extend to support optional funnel steps that are counted but do not block progression.
Full Details
Problem
You have a list of user events (user_id, event_name, timestamp). A conversion funnel is defined as an ordered list of event names. A user "completes" step i of the funnel if they fired event i after completing step i-1 (events must occur in order but not necessarily consecutively). Compute the number of unique users who reached each step.
python
def funnel_count(
events: list[tuple[int, str, int]], # (user_id, event, timestamp)
funnel: list[str]
) -> list[int]:
"""Return list of user counts at each funnel step."""
pass
**Input**:
events = [(1,"view",1),(1,"click",2),(1,"purchase",3),
(2,"view",1),(2,"click",4),
(3,"view",2)]
funnel = ["view", "click", "purchase"]
**Output**: [3, 2, 1]
# All 3 users reached step 1 (view)
# Users 1,2 reached step 2 (click)
# Only user 1 reached step 3 (purchase)
Follow-ups
- How do you write this as a SQL query using self-joins or window functions?
- If the funnel must be completed within a time window (e.g., 7 days), how does your logic change?
- How would you compute conversion rates and visualize the drop-off percentages?
- Extend to support optional funnel steps that are counted but do not block progression.
About This Question
This is a candidate experience report from a faire interview during the onsite round.