Compare Campaign Performance Periods | Dataford Interview Questions

Your question is Compare Campaign Performance Periods. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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.

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

Your question is Compare Campaign Performance Periods. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

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.

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