Task
BrightAds wants to compare campaign performance between two monthly time periods. Write a PostgreSQL query to calculate campaign metrics for January 2024 and February 2024, then show the change between the two periods.
Requirements
- Return one row per campaign that had at least one send in either January 2024 or February 2024.
- For each campaign, calculate:
jan_sends, jan_conversions, jan_conversion_rate
feb_sends, feb_conversions, feb_conversion_rate
- Compute
conversion_rate_change as feb_conversion_rate - jan_conversion_rate.
- Include only campaigns where total sends across the two months are at least 2.
- Order results by
conversion_rate_change descending, then campaign_name ascending.
Use campaigns for campaign metadata and campaign_events for send/conversion activity. A conversion is an event where event_type = 'conversion'. Ignore rows where event_date is NULL.
Table Definitions
campaigns
| column | type | description |
|---|
| campaign_id | INT | Primary key |
| campaign_name | VARCHAR(100) | Campaign name |
| channel | VARCHAR(50) | Marketing channel |
| start_date | DATE | Campaign launch date |
campaign_events
| column | type | description |
|---|
| event_id | INT | Primary key |
| campaign_id | INT | References campaigns.campaign_id |
| event_date | DATE | Date of the event |
| event_type | VARCHAR(20) | Event type such as send or conversion |
| user_id | INT | User tied to the event |
Sample Data
campaigns
| campaign_id | campaign_name | channel | start_date |
|---|
| 1 | Winter Sale | Email | 2023-12-20 |
| 2 | Spring Launch | Paid Search | 2024-01-10 |
| 3 | Reactivation | SMS | 2024-01-05 |
campaign_events
| event_id | campaign_id | event_date | event_type | user_id |
|---|
| 101 | 1 | 2024-01-03 | send | 1001 |
| 102 | 1 | 2024-01-05 | conversion | 1001 |
| 103 | 1 | 2024-02-02 | send | 1002 |
| 104 | 1 | 2024-02-10 | conversion | 1002 |
| 105 | 2 | 2024-01-15 | send | 1003 |
| 106 | 2 | 2024-02-18 | send | 1004 |
Expected Output
| campaign_id | campaign_name | jan_sends | jan_conversions | jan_conversion_rate | feb_sends | feb_conversions | feb_conversion_rate | conversion_rate_change |
|---|
| 1 | Winter Sale | 2 | 1 | 0.5000 | 2 | 2 | 1.0000 | 0.5000 |
| 2 | Spring Launch | 1 | 0 | 0.0000 | 2 | 1 | 0.5000 | 0.5000 |