Rolling 30-Day Average Performance | Dataford Interview Questions

Tatari analysts need to monitor campaign performance over time. Write a PostgreSQL query that calculates a rolling 30-day average of each active campaign's daily conversion rate.

Requirements

Join campaigns to campaign_daily_performance and include only active campaigns with performance dates from January 1 through February 10, 2025.
Calculate each day's conversion rate as conversions divided by impressions, returning NULL when impressions are zero or missing.
Calculate the average of the current day and the preceding 29 calendar days for each campaign. Do not treat missing dates as zero-performance days.
Return one row per campaign performance date, with percentages rounded to two decimals, ordered by campaign and date.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Tatari campaign name
status	VARCHAR(20)	Campaign lifecycle status
channel	VARCHAR(40)	Primary advertising channel

campaign_daily_performance

Column	Type	Description
performance_idPK	INT	Unique daily performance record identifier
campaign_id	INT	References campaigns.campaign_id
performance_date	DATE	Date represented by the performance record
impressions	INT	Number of delivered impressions
conversions	INT	Attributed conversions

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