Click-Through Rate Per Campaign | Dataford Interview Questions

Given two tables (Campaigns and Impressions), how would you find the click-through rate per campaign?

Assume each impression has a Boolean clicked value. Include campaigns with no impressions, treat their impression and click counts as zero, and calculate click-through rate as clicks divided by impressions, expressed as a percentage.

Output

One row per campaign, including campaigns with no impressions
Columns: campaign_id, campaign_name, impression_count, click_count, and click_through_rate
Sort by campaign_id ascending

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(150)	Campaign display name

impressions

Column	Type	Description
impression_idPK	INT	Unique impression identifier
campaign_id	INT	Campaign associated with the impression
clicked	BOOLEAN	Whether the impression generated a click

Your question is Click-Through Rate Per Campaign. 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