CTR by Campaign and Device | Dataford Interview Questions

LG Ad Solutions needs to compare campaign click-through rates across device types. Write a SQL query that calculates aggregated impressions, clicks, and CTR for each campaign and device combination during the 30-day period ending on September 15, 2026.

Output

One row per campaign and device type, with columns campaign_name, device_type, impressions, clicks, and ctr_percent.
Include only performance records from August 16, 2026 through September 15, 2026, inclusive. Treat NULL clicks as zero, and return no CTR for groups whose total impressions are zero.
Sort by campaign name alphabetically, then device type alphabetically, with NULL device types last.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(150)	Campaign display name
advertiser_name	VARCHAR(150)	Advertiser associated with the campaign

campaign_daily_performance

Column	Type	Description
performance_idPK	INT	Unique daily performance record
campaign_id	INT	Campaign associated with the performance record
performance_date	DATE	Date represented by the metrics
device_type	VARCHAR(40)	Device category receiving the ad impression
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks

Your question is CTR by Campaign and Device. 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