Conversion Rate SQL Join | Dataford Interview Questions

Business Context

Amplify Marketing Agency needs a campaign performance view that compares the number of customers assigned to each campaign with the number who converted.

Task

Write a PostgreSQL query that joins customers, campaigns, and conversions to calculate conversion performance for every campaign.

Requirements

Return every campaign, including campaigns with no assigned customers or conversions.
Show assigned customer count, distinct converted customer count, and conversion rate as a percentage rounded to two decimals.
Count each customer at most once per campaign, even if the customer has multiple conversion events.
Order results by campaign_id ascending.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Amplify campaign name
channel	VARCHAR(50)	Marketing channel

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
campaign_id	INT	Campaign assigned to the customer

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion event identifier
customer_id	INT	Customer associated with the conversion
campaign_id	INT	Campaign credited for the conversion
converted_at	DATE	Date of the conversion event

Your question is Conversion Rate SQL Join. Start with the requirements and the three tables in the Question tab.

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

Problem