Top Channels by Conversion Rate

MediumSQL · PostgreSQL

Problem

Rocket's Marketing Analytics team wants to evaluate acquisition performance for the Rocket App. Write a PostgreSQL query that identifies the top three marketing channels by monthly conversion rate.

Requirements

Derive the month from each session's session_at timestamp.
Calculate conversion rate as distinct converted sessions divided by distinct sessions, multiplied by 100.
Prevent multiple conversion events for one session from inflating the conversion count.
Rank channels independently within each month using ROW_NUMBER(), ordering by conversion rate descending, then session volume descending, then channel name ascending.
Return only the top three channels for each month from January through March 2025.

Schema

channels

Column	Type	Description
channel_idPK	INT	Marketing channel identifier
channel_name	VARCHAR(100)	Marketing channel name

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
channel_id	INT	Channel associated with the campaign
campaign_name	VARCHAR(150)	Campaign name

sessions

Column	Type	Description
session_idPK	BIGINT	Rocket App visitor session identifier
campaign_id	INT	Attributed campaign identifier
session_at	TIMESTAMPTZ	Session start timestamp

conversions

Column	Type	Description
conversion_idPK	BIGINT	Conversion event identifier
session_id	BIGINT	Session that generated the conversion
converted_at	TIMESTAMPTZ	Conversion event timestamp
conversion_type	VARCHAR(50)	Type of conversion event

Tableschannelscampaignssessionsconversions