SQL Join for Campaign and Conversion

MediumSQL · PostgreSQL

Problem

Andreessen Horowitz uses campaign performance data to evaluate outreach across its founder, technology, and investment content. Write a PostgreSQL query that joins campaign touchpoints with customer conversion records and reports performance for campaigns launched in Q1 2025.

Requirements

Include every Q1 2025 campaign, including campaigns with no recorded touchpoints.
Count distinct customers reached by each campaign.
Count a customer as converted when a conversion occurred within 30 days of that customer's first touch for the campaign.
Calculate the conversion rate as a percentage, returning 0.00 for campaigns with no reached customers. Sort by conversion rate descending, then campaign ID.

Representative sample data

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(40)	Marketing channel
launch_date	DATE	Campaign launch date
budget	DECIMAL(12,2)	Campaign budget

campaign_touches

Column	Type	Description
touch_idPK	INT	Unique touchpoint identifier
campaign_id	INT	References campaigns.campaign_id
customer_id	INT	Customer receiving the touch
touched_at	DATE	Date of the campaign touch

conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion identifier
customer_id	INT	Customer who converted
conversion_date	DATE	Date of conversion
conversion_type	VARCHAR(40)	Type of conversion

Tablescampaignscampaign_touchesconversions