Conversion Rate Change with CTEs | Dataford Interview Questions

Your question is Conversion Rate Change with CTEs. Start with the requirements and the three tables on the right.

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

Problem

Context

Capgemini Government Solutions wants to evaluate whether product changes improve user conversion behavior. Each product change should be compared with activity during the 30 days immediately before and after the change.

Task

Write a PostgreSQL query using CTEs and joins to compare conversion rates for every valid product change.

Requirements

Classify each visit as before or after based on a 30-day window around change_date.
Use a LEFT JOIN so products with no visits remain in the result.
Calculate visits, converted visits, and conversion rates for both periods. Use NULL for a rate when the period has no visits.
Return the conversion-rate change as after_rate - before_rate, ordered by product_id.

Schema

product_changes

Column	Type	Description
change_idPK	INT	Unique product change identifier
product_id	INT	Product associated with the change
change_date	DATE	Date the product change took effect
change_label	VARCHAR(100)	Short description of the change

product_visits

Column	Type	Description
visit_idPK	INT	Unique product visit identifier
product_id	INT	Product viewed during the visit
visitor_id	INT	Anonymous or authenticated visitor identifier
visited_at	DATE	Date of the product visit

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion identifier
visit_id	INT	Visit that produced the conversion
converted_at	DATE	Date of conversion