7-Day Rolling Average Conversions | Dataford Interview Questions

Corsair's Marketing Analytics team wants to monitor conversion trends for a campaign promoting the Corsair K70 RGB keyboard. Write a PostgreSQL query that reports daily conversions and smooths short-term variation with a 7-day rolling average.

Requirements

Return every date from June 1 through June 7, 2025, including dates with zero conversions.
Restrict results to the Corsair K70 RGB campaign by joining the campaign metadata table.
Count conversions per day and calculate the average of the current day and up to the six preceding calendar days.
Return results in chronological order and round the rolling average to two decimal places.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign or promoted Corsair product name
channel	VARCHAR(50)	Marketing acquisition channel

campaign_conversions

Column	Type	Description
conversion_idPK	INTEGER	Unique conversion event identifier
campaign_id	INTEGER	Campaign associated with the conversion
conversion_date	DATE	Date on which the conversion occurred
source	VARCHAR(50)	Detailed traffic source

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