SQL for Campaign Performance | Dataford Interview Questions

Write a SQL query to analyze campaign performance by advertiser, including spend, impressions, clicks, CTR, and conversion rate over the last 30 days.

Use the provided advertisers and campaign_daily_metrics tables. CTR and conversion rate should be percentages, with zero returned when the denominator is zero.

Output

One row per advertiser, including advertisers with no qualifying activity.
Columns: advertiser_id, advertiser_name, total_spend, total_impressions, total_clicks, ctr, conversion_rate.
Sort by total_spend descending, then advertiser_id ascending.

Schema

advertisers

Column	Type	Description
advertiser_idPK	INT	Unique advertiser identifier
advertiser_name	VARCHAR(120)	Advertiser display name

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record identifier
advertiser_id	INT	Advertiser associated with the metric record
campaign_id	INT	Samsung Ads campaign identifier
metric_date	DATE	Date represented by the daily metrics
spend	NUMERIC(12,2)	Campaign spend in dollars
impressions	INT	Ad impressions
clicks	INT	Ad clicks
conversions	INT	Attributed conversions

Your question is SQL for Campaign Performance. 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