SQL Conversion Rate by Channel | Dataford Interview Questions

L.L.Bean marketing analysts need daily conversion reporting for campaign channels such as email, SMS, paid search, and social media. A customer is considered converted when they have a completed order on the same date as a delivered campaign touch.

Write a PostgreSQL query that calculates campaign conversion rate by touch date and channel for March 1 through March 3, 2026.

Requirements

Include every delivered campaign channel and date, including groups with zero completed orders.
Count distinct reached customers and distinct converted customers.
Calculate conversion_rate as converted customers divided by reached customers, expressed as a percentage rounded to two decimals.
Display missing channel values as Unassigned, and sort by date followed by channel.

Schema

campaign_touches

Column	Type	Description
touch_idPK	INT	Unique identifier for a campaign touch
customer_id	INT	Customer who received the touch
touch_date	DATE	Date the campaign touch was sent
channel	VARCHAR(40)	Marketing channel used for the touch
campaign_name	VARCHAR(100)	Name of the L.L.Bean campaign
delivered	BOOLEAN	Whether the touch was successfully delivered

orders

Column	Type	Description
order_idPK	INT	Unique identifier for an order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Current order status
order_total	NUMERIC(10,2)	Total value of the order

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