SQL Conversion Rate by Campaign | Dataford Interview Questions

Your question is SQL Conversion Rate by Campaign. 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

Google DeepMind marketing analysts need a daily view of campaign performance across surfaces such as the Gemini app and AlphaFold communications. Write a PostgreSQL query that calculates conversion rate by campaign and event date.

Requirements

Count distinct users who generated a click and a conversion for each campaign and date.
Define conversion rate as converting users divided by clicking users, expressed as a percentage rounded to two decimal places.
Include only dates with at least one clicking user, restrict results to January 1 through January 2, 2025, and order by campaign ID and date.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Primary key for the campaign
campaign_name	VARCHAR(100)	Readable campaign or marketing surface name
channel	VARCHAR(50)	Marketing channel used by the campaign

campaign_events

Column	Type	Description
event_idPK	INT	Primary key for the event
campaign_id	INT	Campaign associated with the event
user_id	INT	User who generated the event
event_date	DATE	Calendar date on which the event occurred
event_type	VARCHAR(20)	Event classification, such as click or conversion