Monthly Conversion Rate by Channel | Dataford Interview Questions

Your question is Monthly Conversion Rate by Channel. Start with the requirements and the two tables 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

Business Context

ZoomInfo marketing analysts use website, campaign, and webinar engagement data to evaluate which acquisition channels generate demo requests.

Task

Write a PostgreSQL query that calculates monthly conversion performance by channel from marketing_events. Treat a visitor as converted within a month and channel when they have at least one demo_request event in that same month and channel.

Requirements

Return the month in YYYY-MM format, the channel name, total distinct visitors, converted distinct visitors, and conversion rate as a percentage.
Count each visitor only once per month and channel, even if the visitor has multiple events.
Restrict the analysis to January and February 2025, and exclude events whose channel_id does not match the channels table.
Sort results chronologically by month and alphabetically by channel name.

Schema

marketing_events

Column	Type	Description
event_idPK	INT	Unique marketing event identifier
visitor_id	VARCHAR(30)	Anonymous or known visitor identifier
channel_id	INT	Marketing channel associated with the event
occurred_at	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(30)	Type of marketing event

channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(80)	Readable marketing channel name