TikTok·Data Scientist·Technical Phone Screen
- Write PostgreSQL to analyze a 4-step shopping funnel (view_product, add_to_cart, checkout_start, purchase) across multiple tables. For each country and event date, compute step-to-step conversion rates after deduplicating to the first occurrence per user, session, and product, and only counting a conversion if both steps appear in timestamp order within the same user-session-product group.
- For each user in a given month, identify their most frequent drop-off step across sessions, where a drop-off means the last funnel step reached without the next step occurring in the same session or within 24 hours by the same user and product. Also compute the median elapsed time from the prior step to that drop-off across their sessions.
- For each day in a given month and by country, compute a 7-day rolling conversion rate from add_to_cart to purchase. The denominator is unique user-product add_to_cart events on day D, and the numerator is the subset where the same user and product has a purchase event within 7 days after the add_to_cart timestamp, across any session.
“This took me way longer than it should have.”