SQL Rolling 7-Day CTR | Dataford Interview Questions

Given a table of user ad-click events, write a SQL query to calculate the rolling 7-day click-through rate (CTR) for each campaign. Use calendar dates, including the current date and previous six days. Count only click and impression events, and return NULL when the rolling impression count is zero.

Output

One row per campaign and event date with at least one valid event
Columns: campaign_id, event_date, and rolling_ctr
rolling_ctr is rolling clicks divided by rolling impressions, rounded to four decimal places
Order by campaign_id, then event_date

Schema

ad_events

Column	Type	Description
event_idPK	INT	Unique identifier for the ad event
campaign_id	VARCHAR(50)	Campaign associated with the event
event_date	DATE	Calendar date on which the event occurred
event_type	VARCHAR(20)	Event classification, either click or impression

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

Output

One row per campaign and event date with at least one valid event
Columns: campaign_id, event_date, and rolling_ctr
rolling_ctr is rolling clicks divided by rolling impressions, rounded to four decimal places
Order by campaign_id, then event_date

Schema

ad_events

Column	Type	Description
event_idPK	INT	Unique identifier for the ad event
campaign_id	VARCHAR(50)	Campaign associated with the event
event_date	DATE	Calendar date on which the event occurred
event_type	VARCHAR(20)	Event classification, either click or impression

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