SQL 7-Day Rolling Conversion Average | Dataford Interview Questions

Write a SQL query to calculate a 7-day rolling average of campaign conversions.

Use campaign_conversions as the source table. Treat NULL conversion values as zero, aggregate multiple records for the same campaign and date, and calculate the average across the current date and preceding six calendar days.

Output

One row per campaign and conversion date represented in the data
Columns: campaign_name, conversion_date, daily_conversions, rolling_7_day_avg
Round the rolling average to two decimal places and order by campaign name, then conversion date

Schema

campaign_conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion record identifier
campaign_name	VARCHAR(100)	Campaign associated with the conversion
conversion_date	DATE	Date of the conversion record
conversions	INT	Number of conversions recorded

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the one table are in the Question tab.

Problem

Write a SQL query to calculate a 7-day rolling average of campaign conversions.

Output

One row per campaign and conversion date represented in the data
Columns: campaign_name, conversion_date, daily_conversions, rolling_7_day_avg
Round the rolling average to two decimal places and order by campaign name, then conversion date

Schema

campaign_conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion record identifier
campaign_name	VARCHAR(100)	Campaign associated with the conversion
conversion_date	DATE	Date of the conversion record
conversions	INT	Number of conversions recorded

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the one table are in the Question tab.