CTR vs 7-Day Moving Average | Dataford Interview Questions

How would you use SQL window functions to compare a publisher's current-day CTR with its 7-day moving average?

Use the supplied daily publisher metrics and publisher reference data. Calculate the comparison for every available publisher and date, including dates where CTR cannot be calculated.

Output

One row per publisher and metric date.
Columns: publisher_id, publisher_name, metric_date, current_ctr, moving_avg_7d, and ctr_vs_moving_avg.
Include the current date in the seven-day average, use the available preceding six calendar days, and ignore NULL CTR values in the average.
Order by publisher_id, then metric_date ascending.

Schema

publishers

Column	Type	Description
publisher_idPK	INT	Unique publisher identifier
publisher_name	VARCHAR(100)	Publisher display name

publisher_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record identifier
publisher_id	INT	Publisher associated with the metric
metric_date	DATE	Date of the daily metric
clicks	INT	Number of ad clicks
impressions	INT	Number of ad impressions

Your question is CTR vs 7-Day Moving Average. 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