Rank Channels by Monthly Conversion | Dataford Interview Questions

Write a SQL query using window functions to rank marketing channels by their conversion rate within each calendar month at Prosper Marketplace.

Use the provided channel and marketing event data. Define conversion rate as conversions divided by visits. Exclude channel-month combinations with no visits.

Output

One row per channel and calendar month, with month_start, channel_name, visits, conversions, conversion_rate, and monthly_rank.
Rank highest conversion rates first within each month; tied rates share a rank.
Order by month_start, monthly_rank, then channel_name.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Prosper marketing channel name

marketing_events

Column	Type	Description
event_idPK	INT	Unique marketing event identifier
channel_id	INT	Marketing channel associated with the event
occurred_at	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(30)	Event classification, either visit or conversion

Your question is Rank Channels by Monthly Conversion. 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