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.
Write a PostgreSQL query using CTEs and joins to compare conversion rates for every valid product change.
before or after based on a 30-day window around change_date.LEFT JOIN so products with no visits remain in the result.NULL for a rate when the period has no visits.after_rate - before_rate, ordered by product_id.| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| conversion_idPK | INT | Unique conversion identifier |
| visit_id | INT | Visit that produced the conversion |
| converted_at | DATE | Date of conversion |