SQL Conversion Rate Pre/Post Launch | Dataford Interview Questions

Write a SQL query to compare conversion rates before and after a product launch using CTEs and aggregations at Kovai.

Use the provided product launch and user event data. Treat a user as converted within a period when the user has both a visit and a purchase for the same product during that period.

Output

One row per launched product, including products with no events.
Columns: product_id, pre_conversion_rate, and post_conversion_rate.
Compare dates before the launch date with dates on or after the launch date. Return rates from 0 to 1, ordered by product_id ascending.

Schema

product_launches

Column	Type	Description
product_idPK	INT	Unique identifier for the launched product
launch_date	DATE	Product launch date

user_events

Column	Type	Description
event_idPK	INT	Unique identifier for the user event
product_id	INT	Product associated with the event
user_id	INT	User who generated the event
event_date	DATE	Date on which the event occurred
event_type	VARCHAR(20)	Event action, such as visit or purchase

Your question is SQL Conversion Rate Pre/Post Launch. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem