Rolling 7-Day CTR With Windows | Dataford Interview Questions

Write a SQL query using window functions to calculate rolling 7-day CTR by campaign and compare it to the prior period for Known reporting.

Use the provided campaign and daily metric tables. Treat missing or NULL clicks as zero, and only return dates with a complete current 7-day period and complete preceding 7-day period.

Output

One row per campaign and report date, ordered by campaign_id, then report_date.
Columns: campaign_id, report_date, rolling_clicks, rolling_impressions, rolling_ctr, prior_period_ctr, and ctr_change_pct.
CTR is clicks divided by impressions. Return NULL when the denominator is zero.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Known reporting

campaign_daily_metrics

Column	Type	Description
campaign_id	INT	Campaign associated with the daily metrics
metric_date	DATE	Date of the campaign metrics
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks

Your question is Rolling 7-Day CTR With Windows. 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