Daily CTR by Campaign Category | Dataford Interview Questions

Given a database schema for ad impressions and clicks, write a query to calculate the daily click-through rate (CTR) grouped by campaign category.

Use the provided tables and include dates and categories with impressions even when no clicks occurred. CTR should be expressed as a percentage.

Output

One row per impression date and campaign category.
Columns: campaign_category, impression_date, impressions, clicks, and ctr_percentage.
Include zero-click groups, exclude clicks that do not match an impression, and order by impression_date, then campaign_category.

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
category	VARCHAR(50)	Campaign category

ad_impressions

Column	Type	Description
impression_idPK	INT	Unique impression identifier
campaign_id	INT	Campaign associated with the impression
impression_date	TIMESTAMP	Timestamp when the impression occurred

ad_clicks

Column	Type	Description
click_idPK	INT	Unique click identifier
impression_id	INT	Impression associated with the click
clicked_at	TIMESTAMP	Timestamp when the click occurred

Your question is Daily CTR by Campaign Category. Start with the requirements and the three 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.