Compare Campaign Performance Across Months

MediumSQL · PostgreSQL

Problem

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.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign display name
channel	VARCHAR(50)	Marketing channel used for the campaign
start_date	DATE	Campaign launch date

campaign_events

Column	Type	Description
event_idPK	INT	Unique event identifier
campaign_id	INT	Campaign tied to the event
event_date	DATE	Date the event occurred
event_type	VARCHAR(20)	Type of event such as send or conversion
user_id	INT	User associated with the event

Tablescampaignscampaign_events