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_ratefeb_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 |
