Analyze shopping funnel with joins and windows

Q: Analyze shopping funnel with joins and windows

This is a Data Manipulation (SQL/Python) interview question from ByteDance for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Write SQL (PostgreSQL) to analyze a 4-step shopping funnel: view_product → add_to_cart → checkout_start → purchase. Use the schema and sample data below. Assume all timestamps are UTC and duplicates can occur. Schema: users(user_id INT, country TEXT); sessions(session_id INT, user_id INT, session_start TIMESTAMP); events(session_id INT, user_id INT, event_time TIMESTAMP, event_type TEXT, product_id TEXT); orders(order_id INT, user_id INT, session_id INT, order_time TIMESTAMP, revenue NUMERIC(10,2)). Sample rows: users: [ (1,'US'), (2,'US'), (3,'CA'), (4,'US') ]; sessions: [ (10,1,'2025-08-01 10:00'), (11,1,'2025-08-02 09:00'), (12,2,'2025-08-02 12:00'), (13,3,'2025-08-03 18:00'), (14,4,'2025-08-03 20:00') ]; events: [ (10,1,'2025-08-01 10:01','view_product','A'), (10,1,'2025-08-01 10:02','add_to_cart','A'), (10,1,'2025-08-01 10:05','checkout_start','A'), (10,1,'2025-08-01 10:06','purchase','A'), (11,1,'2025-08-02 09:05','view_product','B'), (11,1,'2025-08-02 09:06','add_to_cart','B'), (11,1,'2025-08-02 09:06','add_to_cart','B'), (11,1,'2025-08-02 09:10','checkout_start','B'), (12,2,'2025-08-02 12:01','view_product','C'), (12,2,'2025-08-02 12:02','add_to_cart','C'), (12,2,'2025-08-02 12:20','view_product','D'), (13,3,'2025-08-03 18:10','view_product','A'), (14,4,'2025-08-03 20:03','view_product','E'), (14,4,'2025-08-03 20:05','add_to_cart','E'), (14,4,'2025-08-03 20:06','purchase','E') ]; orders: [ (501,1,10,'2025-08-01 10:06',39.99), (502,4,14,'2025-08-03 20:06',12.00) ]. Tasks: 1) By country and event_date (DATE(event_time)), compute step-to-step conversion rates view→add, add→checkout, checkout→purchase for 2025-08-01 to 2025-08-07. Deduplicate to the first occurrence of each step per (user_id, session_id, product_id). Only count a step-to-step conversion if both steps exist in timestamp order within the same (user_id, session_id, product_id). 2) For each user, in 2025-08, find their most frequent drop-off step (the last step reached in a session without reaching the next step either in the same session or within 24 hours by the same user and product). Also compute the median elapsed time from the preceding step to that drop-off across their sessions. 3) For each day in 2025-08, compute a 7-day rolling conversion rate from add_to_cart to purchase by country. Denominator: unique (user_id, product_id) add_to_cart events on day D. Numerator: those with a purchase by the same user and product within 7 days after the add_to_cart timestamp (across any session). Use window functions for deduping, step-chaining, and rolling calculations; be explicit about tie-breaking when multiple events share the same timestamp.

Analyze shopping funnel with joins and windows

Comments (0)