SQL Conversion Rate by Channel | Dataford Interview Questions

Your question is SQL Conversion Rate by Channel. Start with the requirements and the two 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

Johnson & Johnson marketing analysts need to compare conversion performance across channels for campaigns promoting brands such as TYLENOL, Neutrogena, and BAND-AID Brand. Write a PostgreSQL query that summarizes eligible campaigns during Q1 2025.

Requirements

Include campaigns active at any point between January 1 and March 31, 2025.
Group results by marketing channel, labeling a NULL channel as Unattributed.
Return distinct lead count, distinct conversion count, and conversion rate as a percentage rounded to two decimals.
Include eligible channels with campaigns but no leads, and order by conversion rate descending, then channel ascending.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
product_brand	VARCHAR(50)	Johnson & Johnson brand promoted
start_date	DATE	Campaign start date
end_date	DATE	Campaign end date, or NULL if ongoing

campaign_leads

Column	Type	Description
lead_idPK	INTEGER	Unique lead identifier
campaign_id	INTEGER	Campaign associated with the lead
lead_date	DATE	Date the lead was acquired
converted_at	DATE	Date the lead converted, or NULL if not converted