User Action Journey: Reconstruct and Analyze User Funnels from Raw Event Logs
Interview Experience
Problem
You have a table of user events: (user_id, event_name, ts). A funnel is an ordered list of event names that a user must complete in sequence (though non-funnel events may occur in between). For each user, determine whether they completed the funnel and, if so, the time elapsed from the first to last funnel step.
sql
-- events: user_id VARCHAR, event_name VARCHAR, ts TIMESTAMP
--
**Example** funnel: ['view_product', 'add_to_cart', 'checkout', 'purchase']
SELECT
user_id,
MIN(CASE WHEN event_name = 'view_product' THEN ts END) AS step1_ts,
MIN(CASE WHEN event_name = 'add_to_cart'
AND ts > MIN(CASE WHEN event_name = 'view_product' THEN ts END)
THEN ts END) AS step2_ts
-- ... continue for each step
FROM events
GROUP BY user_id;
Example
user_id | event_name | ts
alice | view_product | 10:00
alice | browse | 10:05 <- not in funnel, skipped
alice | add_to_cart | 10:10
alice | purchase | 10:30 <- skipped checkout; funnel incomplete
bob | view_product | 09:00
bob | add_to_cart | 09:05
bob | checkout | 09:10
bob | purchase | 09:15 -> completed in 15 min
Follow-ups
- How do you define and handle funnel re-entry (user completes the funnel twice)?
- What query pattern works best for this in BigQuery (ARRAY_AGG + filtering vs. self-joins)?
- How would you compute funnel conversion rates by user segment (e.g., mobile vs. desktop)?
Full Details
Problem
You have a table of user events: (user_id, event_name, ts). A funnel is an ordered list of event names that a user must complete in sequence (though non-funnel events may occur in between). For each user, determine whether they completed the funnel and, if so, the time elapsed from the first to last funnel step.
sql
-- events: user_id VARCHAR, event_name VARCHAR, ts TIMESTAMP
--
**Example** funnel: ['view_product', 'add_to_cart', 'checkout', 'purchase']
SELECT
user_id,
MIN(CASE WHEN event_name = 'view_product' THEN ts END) AS step1_ts,
MIN(CASE WHEN event_name = 'add_to_cart'
AND ts > MIN(CASE WHEN event_name = 'view_product' THEN ts END)
THEN ts END) AS step2_ts
-- ... continue for each step
FROM events
GROUP BY user_id;
Example
user_id | event_name | ts
alice | view_product | 10:00
alice | browse | 10:05 <- not in funnel, skipped
alice | add_to_cart | 10:10
alice | purchase | 10:30 <- skipped checkout; funnel incomplete
bob | view_product | 09:00
bob | add_to_cart | 09:05
bob | checkout | 09:10
bob | purchase | 09:15 -> completed in 15 min
Follow-ups
- How do you define and handle funnel re-entry (user completes the funnel twice)?
- What query pattern works best for this in BigQuery (ARRAY_AGG + filtering vs. self-joins)?
- How would you compute funnel conversion rates by user segment (e.g., mobile vs. desktop)?
About This Question
This is a candidate experience report from a whatnot interview during the phone round.
It covers the following topics: Coding, Sql, Phone, Onsite .