SQL 7-Day Rolling Average | Dataford Interview Questions

Keyrus France's Data Intelligence reporting needs a daily view of conversions from active campaigns. Write a PostgreSQL query for the period from 2024-06-01 through 2024-06-10.

Requirements

Count only events with conversion_status = 'converted' belonging to active campaigns.
Return every calendar date in the period, including dates with zero conversions.
Calculate a seven-day rolling average using the current date and up to the six preceding calendar days.
Return conversion_date, daily_conversions, and rolling_7_day_average, ordered chronologically. Round the average to two decimal places.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign display name
status	VARCHAR(20)	Campaign lifecycle status

conversion_events

Column	Type	Description
event_idPK	INTEGER	Unique conversion event identifier
campaign_id	INTEGER	Campaign associated with the event
converted_at	TIMESTAMP	Timestamp when the event occurred
conversion_status	VARCHAR(20)	Conversion processing outcome

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