Compare Campaign Performance Across Months

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

Task

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

Task

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

Task

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

Interview Guides

Task

Requirements

Table Definitions

campaigns

campaign_events

Sample Data

campaigns

campaign_events

Expected Output

Compare Campaign Performance Across Months

Task

Requirements

Table Definitions

campaigns

campaign_events

Sample Data

campaigns

campaign_events

Expected Output

Practice SQL

Compare Campaign Performance Across Months

Task

Requirements

Table Definitions

campaigns

campaign_events

Sample Data

campaigns

campaign_events

Expected Output

Compare Campaign Performance Across Months

Task

Requirements

Table Definitions

campaigns

campaign_events

Sample Data

campaigns

campaign_events

Expected Output

Practice SQL