Rank Campaign Segments by Conversion | Dataford Interview Questions

Your question is Rank Campaign Segments by Conversion. Start with the requirements and the three tables 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

NovaCart ran a marketing campaign and wants to know which customer segment converted best. Write a SQL query to identify the highest-converting segment for a specific campaign.

Requirements

Consider only customers who were sent campaign CMP100.
Define a conversion as a purchase made within 7 days after the campaign send date.
Return one row per customer segment with:
- total targeted customers
- number of converted customers
- conversion rate as a percentage rounded to 2 decimals
- rank by conversion rate, highest first
Exclude segments with fewer than 2 targeted customers.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment	VARCHAR(50)	Customer marketing segment
signup_date	DATE	Customer signup date

campaign_sends

Column	Type	Description
send_idPK	INT	Unique campaign send record
campaign_id	VARCHAR(20)	Campaign identifier
customer_id	INT	Customer who received the campaign
send_date	DATE	Date the campaign was sent

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_amount	DECIMAL(10,2)	Order value