SQL Conversion Rate Comparison | Dataford Interview Questions

Write a SQL query to compare conversion rates before and after a product change using joins and aggregations.

Use the supplied tables to classify sessions before or after each product's change date. A conversion is counted when a session has at least one matching conversion record. Treat the period before the change as dates earlier than the change date, and the period after as dates on or after it.

Output

One row per product change.
Include product_id, before and after session counts, conversion counts, conversion rates, and the rate change.
Return products without sessions with zero counts and NULL rates. Order by product_id ascending.

Schema

product_changes

Column	Type	Description
product_idPK	INT	Product identifier
change_date	DATE	Date the product change became active

sessions

Column	Type	Description
session_idPK	INT	Unique user session identifier
product_id	INT	Product viewed during the session
user_id	VARCHAR(50)	User associated with the session
session_date	DATE	Date of the session

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
session_id	INT	Session that produced the conversion
conversion_date	DATE	Date of the conversion event

Your question is SQL Conversion Rate Comparison. Start with the requirements and the three 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