SQL Conversion Rate Comparison | Dataford Interview Questions

Write a SQL query to compare conversion rates before and after a product change using grouped time windows.

Use a 14-day observation period, divided into two seven-day windows before and after each change. Calculate conversion rates only for windows containing events.

Output

One row per product change, period, and seven-day window.
Columns: change_id, period, window_number, sessions, conversions, and conversion_rate.
Order by change_id, window_number, with before preceding after.

Schema

product_changes

Column	Type	Description
change_idPK	INT	Identifier for the product change
product_id	VARCHAR(30)	Product affected by the change
change_date	DATE	Date the product change took effect

conversion_events

Column	Type	Description
event_idPK	INT	Identifier for the conversion event
product_id	VARCHAR(30)	Product associated with the event
event_date	DATE	Date of the event
converted	BOOLEAN	Whether the event converted

Your question is SQL Conversion Rate Comparison. 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