Analyze shopping funnel with joins and windows
Overview
This question evaluates SQL-based data manipulation skills—specifically event deduplication, temporal ordering, joins, and window-function usage for multi-step funnel conversion analysis with session- and user/product-level scoping, and is categorized as Data Manipulation (SQL/Python) for a data scientist role.
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.