Rolling 7-Day CTR Average SQL | Dataford Interview Questions

Write a SQL query to calculate the rolling 7-day average of click-through rates (CTR) for a set of Quantcast campaigns.

Use the supplied campaign, advertiser, and daily campaign metric data. Calculate daily CTR as clicks divided by impressions, then calculate the average across the current date and six preceding calendar days. Exclude non-Quantcast campaigns and dates without metric rows.

Output

One row per Quantcast campaign and metric date.
Columns: campaign_id, campaign_name, metric_date, daily_ctr, and rolling_7_day_ctr.
Sort by campaign_id, then metric_date ascending.

Schema

advertisers

Column	Type	Description
advertiser_idPK	INT	Unique advertiser identifier
advertiser_name	VARCHAR(100)	Advertiser name
account_region	VARCHAR(30)	Advertiser account region

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
advertiser_id	INT	Associated advertiser
campaign_name	VARCHAR(120)	Campaign name
platform	VARCHAR(30)	Advertising platform

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record
campaign_id	INT	Associated campaign
metric_date	DATE	Date of the metrics
clicks	INT	Number of clicks
impressions	INT	Number of impressions

Your question is Rolling 7-Day CTR Average SQL. Start with the requirements and the three 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