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

Your question is SQL 7-Day Rolling Conversion Average. 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

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

Your question is SQL 7-Day Rolling Conversion Average. 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.