Interview Guides

Compare Campaign Performance Periods | Dataford Interview Questions - Dataford - Ace your Interview

Compare Campaign Performance Periods

Easy

SQL & Data Manipulation

Asked at 3 companies3AggregationsCase WhenDate Functions

Also asked at

Problem

Task

Rippling's growth team launched a campaign in Rippling Spend on 2024-02-15 and wants a quick before-vs-after performance read. Write a SQL query to compare daily campaign performance for the periods before and after launch.

Requirements

Use only the campaign_events table.
Return one row per period: Before Launch and After Launch.
For each period, calculate:
- total_impressions
- total_clicks
- total_signups
- ctr = total_clicks / total_impressions
- signup_rate = total_signups / total_clicks
Include only rows where event_date is between 2024-02-08 and 2024-02-21 inclusive.
Treat dates before 2024-02-15 as Before Launch, and dates on or after 2024-02-15 as After Launch.
Order the output by period so After Launch appears first.

Table Definition

column	type	description
event_id	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

event_id	event_date	campaign_name	impressions	clicks	signups	channel
1	2024-02-12	Spend Launch	1000	45	6	Paid Search
2	2024-02-08	Spend Launch	800	32	4	Paid Social
3	2024-02-16	Spend Launch	1500	90	15	Paid Search
4	2024-02-14	Spend Launch	950	40	5	Email
5	2024-02-18	Spend Launch	1700	102	18	Paid Social
6	2024-02-21	Spend Launch	1600	88	14	Email

Expected Output

period	total_impressions	total_clicks	total_signups	ctr	signup_rate
After Launch	7400	430	70	0.0581	0.1628
Before Launch	4400	177	21	0.0402	0.1186

Schema

campaign_events

Column	Type	Description
event_idPK	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

Example 1

Input

{"campaign_events":[["1","2024-02-12","Spend Launch","1000","45","6","Paid Search"],["2","2024-02-08","Spend Launch","800","32","4","Paid Social"],["3","2024-02-16","Spend Launch","1500","90","15","Paid Search"],["4","2024-02-14","Spend Launch","950","40","5","Email"],["5","2024-02-18","Spend Launch","1700","102","18","Paid Social"],["6","2024-02-21","Spend Launch","1600","88","14","Email"],["7","2024-02-10","Spend Launch","1100","60","6",null],["8","2024-02-15","Spend Launch","1200","70","10","

Output[["After Launch","12754","695","118","0.0545","0.1698"],["Before Launch","10078","476","73","0.0472","0.1534"]]

Problem

Task

Requirements

Use only the campaign_events table.
Return one row per period: Before Launch and After Launch.
For each period, calculate:
- total_impressions
- total_clicks
- total_signups
- ctr = total_clicks / total_impressions
- signup_rate = total_signups / total_clicks
Include only rows where event_date is between 2024-02-08 and 2024-02-21 inclusive.
Treat dates before 2024-02-15 as Before Launch, and dates on or after 2024-02-15 as After Launch.
Order the output by period so After Launch appears first.

Table Definition

column	type	description
event_id	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

event_id	event_date	campaign_name	impressions	clicks	signups	channel
1	2024-02-12	Spend Launch	1000	45	6	Paid Search
2	2024-02-08	Spend Launch	800	32	4	Paid Social
3	2024-02-16	Spend Launch	1500	90	15	Paid Search
4	2024-02-14	Spend Launch	950	40	5	Email
5	2024-02-18	Spend Launch	1700	102	18	Paid Social
6	2024-02-21	Spend Launch	1600	88	14	Email

Expected Output

period	total_impressions	total_clicks	total_signups	ctr	signup_rate
After Launch	7400	430	70	0.0581	0.1628
Before Launch	4400	177	21	0.0402	0.1186

Schema

campaign_events

Column	Type	Description
event_idPK	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

Example 1

Input

{"campaign_events":[["1","2024-02-12","Spend Launch","1000","45","6","Paid Search"],["2","2024-02-08","Spend Launch","800","32","4","Paid Social"],["3","2024-02-16","Spend Launch","1500","90","15","Paid Search"],["4","2024-02-14","Spend Launch","950","40","5","Email"],["5","2024-02-18","Spend Launch","1700","102","18","Paid Social"],["6","2024-02-21","Spend Launch","1600","88","14","Email"],["7","2024-02-10","Spend Launch","1100","60","6",null],["8","2024-02-15","Spend Launch","1200","70","10","

Output[["After Launch","12754","695","118","0.0545","0.1698"],["Before Launch","10078","476","73","0.0472","0.1534"]]

Practice SQL

PostgreSQL

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

Up next

Compare Campaign Performance Across MonthsMedium

Compare Sales Before Product LaunchMedium

Monthly Campaign Performance SummaryEasy

Next question

Compare Campaign Performance Periods

Easy

SQL & Data Manipulation

Asked at 3 companies3AggregationsCase WhenDate Functions

Also asked at

Problem

Task

Requirements

Use only the campaign_events table.
Return one row per period: Before Launch and After Launch.
For each period, calculate:
- total_impressions
- total_clicks
- total_signups
- ctr = total_clicks / total_impressions
- signup_rate = total_signups / total_clicks
Include only rows where event_date is between 2024-02-08 and 2024-02-21 inclusive.
Treat dates before 2024-02-15 as Before Launch, and dates on or after 2024-02-15 as After Launch.
Order the output by period so After Launch appears first.

Table Definition

column	type	description
event_id	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

event_id	event_date	campaign_name	impressions	clicks	signups	channel
1	2024-02-12	Spend Launch	1000	45	6	Paid Search
2	2024-02-08	Spend Launch	800	32	4	Paid Social
3	2024-02-16	Spend Launch	1500	90	15	Paid Search
4	2024-02-14	Spend Launch	950	40	5	Email
5	2024-02-18	Spend Launch	1700	102	18	Paid Social
6	2024-02-21	Spend Launch	1600	88	14	Email

Expected Output

period	total_impressions	total_clicks	total_signups	ctr	signup_rate
After Launch	7400	430	70	0.0581	0.1628
Before Launch	4400	177	21	0.0402	0.1186

Schema

campaign_events

Column	Type	Description
event_idPK	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

Example 1

Input

{"campaign_events":[["1","2024-02-12","Spend Launch","1000","45","6","Paid Search"],["2","2024-02-08","Spend Launch","800","32","4","Paid Social"],["3","2024-02-16","Spend Launch","1500","90","15","Paid Search"],["4","2024-02-14","Spend Launch","950","40","5","Email"],["5","2024-02-18","Spend Launch","1700","102","18","Paid Social"],["6","2024-02-21","Spend Launch","1600","88","14","Email"],["7","2024-02-10","Spend Launch","1100","60","6",null],["8","2024-02-15","Spend Launch","1200","70","10","

Output[["After Launch","12754","695","118","0.0545","0.1698"],["Before Launch","10078","476","73","0.0472","0.1534"]]

Problem

Task

Requirements

Use only the campaign_events table.
Return one row per period: Before Launch and After Launch.
For each period, calculate:
- total_impressions
- total_clicks
- total_signups
- ctr = total_clicks / total_impressions
- signup_rate = total_signups / total_clicks
Include only rows where event_date is between 2024-02-08 and 2024-02-21 inclusive.
Treat dates before 2024-02-15 as Before Launch, and dates on or after 2024-02-15 as After Launch.
Order the output by period so After Launch appears first.

Table Definition

column	type	description
event_id	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

event_id	event_date	campaign_name	impressions	clicks	signups	channel
1	2024-02-12	Spend Launch	1000	45	6	Paid Search
2	2024-02-08	Spend Launch	800	32	4	Paid Social
3	2024-02-16	Spend Launch	1500	90	15	Paid Search
4	2024-02-14	Spend Launch	950	40	5	Email
5	2024-02-18	Spend Launch	1700	102	18	Paid Social
6	2024-02-21	Spend Launch	1600	88	14	Email

Expected Output

period	total_impressions	total_clicks	total_signups	ctr	signup_rate
After Launch	7400	430	70	0.0581	0.1628
Before Launch	4400	177	21	0.0402	0.1186

Schema

campaign_events

Column	Type	Description
event_idPK	INT	Primary key for each daily campaign record
event_date	DATE	Date of campaign activity
campaign_name	VARCHAR(100)	Campaign name in Rippling Spend
impressions	INT	Number of impressions recorded that day
clicks	INT	Number of clicks recorded that day
signups	INT	Number of signups attributed that day
channel	VARCHAR(50)	Marketing channel

Sample Data

Example 1

Input

{"campaign_events":[["1","2024-02-12","Spend Launch","1000","45","6","Paid Search"],["2","2024-02-08","Spend Launch","800","32","4","Paid Social"],["3","2024-02-16","Spend Launch","1500","90","15","Paid Search"],["4","2024-02-14","Spend Launch","950","40","5","Email"],["5","2024-02-18","Spend Launch","1700","102","18","Paid Social"],["6","2024-02-21","Spend Launch","1600","88","14","Email"],["7","2024-02-10","Spend Launch","1100","60","6",null],["8","2024-02-15","Spend Launch","1200","70","10","

Output[["After Launch","12754","695","118","0.0545","0.1698"],["Before Launch","10078","476","73","0.0472","0.1534"]]

Practice SQL

PostgreSQL

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

Up next

Compare Campaign Performance Across MonthsMedium

Compare Sales Before Product LaunchMedium

Monthly Campaign Performance SummaryEasy

Next question