SQL Case With Data Results | Dataford Interview Questions

Describe a case where you used SQL to analyze marketing data and produced results with data.

Using the supplied marketing tables, write a query that summarizes performance for every campaign. Include campaigns without recorded activity and treat missing numeric activity as zero where appropriate.

Output

One row per campaign with campaign_id, campaign_name, channel, total_impressions, total_clicks, total_conversions, total_spend, ctr_pct, and conversion_rate_pct
Include all campaigns, including those without metric rows; rates are NULL when their denominator is zero
Order by total_conversions descending, then campaign_id ascending

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(120)	Campaign display name
channel	VARCHAR(40)	Marketing channel
status	VARCHAR(20)	Campaign status

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record identifier
campaign_id	INT	Referenced campaign identifier
metric_date	DATE	Date of recorded activity
impressions	INT	Number of impressions
clicks	INT	Number of clicks
conversions	INT	Number of conversions
spend	NUMERIC(12,2)	Marketing spend

Your question is SQL Case With Data Results. 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