SQL Conversion Rate with Rolling Average | Dataford Interview Questions

Write a SQL query to calculate monthly campaign conversion rate by channel, including a rolling 3-month average.

Use the provided campaigns and campaign_events tables. Treat unique visitors with an impression event as reached and unique visitors with a conversion event as converted. Include calendar months with no activity and treat their conversion rate as 0.

Output

One row per channel and calendar month.
Columns: channel, month, conversion_rate, and rolling_3_month_avg.
Rates are percentages rounded to two decimal places. Include the current month and preceding two calendar months in the rolling average.
Order by channel ascending, then month ascending.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel associated with the campaign

campaign_events

Column	Type	Description
event_idPK	INT	Unique event identifier
campaign_id	INT	Referenced campaign
visitor_id	INT	Visitor associated with the event
event_type	VARCHAR(30)	Event classification, such as impression or conversion
event_date	DATE	Date the event occurred

Your question is SQL Conversion Rate with 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