30-Day Rolling Conversion SQL | Dataford Interview Questions

Your question is 30-Day Rolling Conversion SQL. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

@Orchard Marketing Analytics needs to monitor how conversion performance changes across acquisition channels. Write a PostgreSQL query that calculates each channel's daily conversion rate and its 30-day rolling average.

Requirements

Aggregate multiple records for the same channel and date before calculating rates.
Calculate conversion rate as conversions divided by visits. Return NULL when visits are zero.
Calculate a calendar-based 30-day rolling average that includes the current date and the preceding 29 days for the same channel.
Ignore NULL daily rates when calculating the rolling average, and round both rates to four decimal places.
Return results ordered by channel and activity date.

Schema

channel_performance

Column	Type	Description
performance_idPK	INTEGER	Unique performance record identifier
channel	VARCHAR(50)	@Orchard acquisition channel
activity_date	DATE	Date associated with the performance record
visits	INTEGER	Number of attributed visits
conversions	INTEGER	Number of completed conversions

Your question is 30-Day Rolling Conversion SQL. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Requirements

Aggregate multiple records for the same channel and date before calculating rates.
Calculate conversion rate as conversions divided by visits. Return NULL when visits are zero.
Calculate a calendar-based 30-day rolling average that includes the current date and the preceding 29 days for the same channel.
Ignore NULL daily rates when calculating the rolling average, and round both rates to four decimal places.
Return results ordered by channel and activity date.

Schema

channel_performance

Column	Type	Description
performance_idPK	INTEGER	Unique performance record identifier
channel	VARCHAR(50)	@Orchard acquisition channel
activity_date	DATE	Date associated with the performance record
visits	INTEGER	Number of attributed visits
conversions	INTEGER	Number of completed conversions