SQL for Weekly Conversions by Channel | Dataford Interview Questions

Write a SQL query to calculate weekly campaign conversions, spend, and conversion rate by channel for Direct Agents.

Use the campaigns and campaign_metrics tables. Conversion rate is conversions divided by clicks, expressed as a percentage. Ignore metric records that do not match a campaign.

Output

One row per week and channel, with week_start, channel, conversions, spend, and conversion_rate.
Include weeks represented by matching campaign metrics, order by week_start ascending and channel ascending.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Direct Agents campaign name
channel	VARCHAR(50)	Marketing channel

campaign_metrics

Column	Type	Description
metric_idPK	INTEGER	Unique campaign metric record
campaign_id	INTEGER	Campaign associated with the metric record
metric_date	DATE	Date on which performance was recorded
clicks	INTEGER	Number of attributed clicks
conversions	INTEGER	Number of attributed conversions
spend	NUMERIC(12,2)	Campaign spend for the metric date

Your question is SQL for Weekly Conversions by Channel. 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