Interview Guides

Compare Campaign Performance Across Months | Dataford Interview Questions - Dataford - Ace your Interview

Compare Campaign Performance Across Months

Medium

SQL & Data Manipulation

Asked at 10 companies10AggregationsCTEsDate Functions

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"campaigns":[["1","Winter Sale","Email","2023-12-20"],["2","Spring Launch","Paid Search","2024-01-10"],["3","Reactivation","SMS","2024-01-05"],["4","Brand Awareness","Social","2023-11-15"],["5","VIP Upsell","Email","2024-02-01"],["6","Dormant Leads",null,"2024-01-25"],["7","Flash Promo","Push","2024-02-14"],["8","Referral Boost","Affiliate",null],["9","App Install Drive","Programmatic","2024-01-31"],["10","Summer Refresh","Reddit","2023-12-08"],["11","Cart Recovery","Email","2024-02-14"],["12",

Output

[["2","Spring Launch","1","0","0.0000","2","2","1.0000","1.0000"],["5","VIP Upsell","2","0","0.0000","1","1","1.0000","1.0000"],["4","Brand Awareness","0","0","null","2","1","0.5000","0.5000"],["1","Winter Sale","2","1","0.5000","2","2","1.0000","0.5000"]]

Problem

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

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

Sample Data

Example 1

Input

{"campaigns":[["1","Winter Sale","Email","2023-12-20"],["2","Spring Launch","Paid Search","2024-01-10"],["3","Reactivation","SMS","2024-01-05"],["4","Brand Awareness","Social","2023-11-15"],["5","VIP Upsell","Email","2024-02-01"],["6","Dormant Leads",null,"2024-01-25"],["7","Flash Promo","Push","2024-02-14"],["8","Referral Boost","Affiliate",null],["9","App Install Drive","Programmatic","2024-01-31"],["10","Summer Refresh","Reddit","2023-12-08"],["11","Cart Recovery","Email","2024-02-14"],["12",

Output

[["2","Spring Launch","1","0","0.0000","2","2","1.0000","1.0000"],["5","VIP Upsell","2","0","0.0000","1","1","1.0000","1.0000"],["4","Brand Awareness","0","0","null","2","1","0.5000","0.5000"],["1","Winter Sale","2","1","0.5000","2","2","1.0000","0.5000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance PeriodsEasy

Conversion Rate by Channel SegmentMedium

Next question

Compare Campaign Performance Across Months

Medium

SQL & Data Manipulation

Asked at 10 companies10AggregationsCTEsDate Functions

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"campaigns":[["1","Winter Sale","Email","2023-12-20"],["2","Spring Launch","Paid Search","2024-01-10"],["3","Reactivation","SMS","2024-01-05"],["4","Brand Awareness","Social","2023-11-15"],["5","VIP Upsell","Email","2024-02-01"],["6","Dormant Leads",null,"2024-01-25"],["7","Flash Promo","Push","2024-02-14"],["8","Referral Boost","Affiliate",null],["9","App Install Drive","Programmatic","2024-01-31"],["10","Summer Refresh","Reddit","2023-12-08"],["11","Cart Recovery","Email","2024-02-14"],["12",

Output

[["2","Spring Launch","1","0","0.0000","2","2","1.0000","1.0000"],["5","VIP Upsell","2","0","0.0000","1","1","1.0000","1.0000"],["4","Brand Awareness","0","0","null","2","1","0.5000","0.5000"],["1","Winter Sale","2","1","0.5000","2","2","1.0000","0.5000"]]

Problem

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

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

Sample Data

Example 1

Input

{"campaigns":[["1","Winter Sale","Email","2023-12-20"],["2","Spring Launch","Paid Search","2024-01-10"],["3","Reactivation","SMS","2024-01-05"],["4","Brand Awareness","Social","2023-11-15"],["5","VIP Upsell","Email","2024-02-01"],["6","Dormant Leads",null,"2024-01-25"],["7","Flash Promo","Push","2024-02-14"],["8","Referral Boost","Affiliate",null],["9","App Install Drive","Programmatic","2024-01-31"],["10","Summer Refresh","Reddit","2023-12-08"],["11","Cart Recovery","Email","2024-02-14"],["12",

Output

[["2","Spring Launch","1","0","0.0000","2","2","1.0000","1.0000"],["5","VIP Upsell","2","0","0.0000","1","1","1.0000","1.0000"],["4","Brand Awareness","0","0","null","2","1","0.5000","0.5000"],["1","Winter Sale","2","1","0.5000","2","2","1.0000","0.5000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance PeriodsEasy

Conversion Rate by Channel SegmentMedium

Next question