SQL Rank Channels by Region

MediumSQL · PostgreSQL

Problem

Stanley Martin Homes wants to compare digital campaign performance across regional marketing territories. Write a PostgreSQL query that aggregates lead and conversion results by region and channel, then ranks channels within each region.

Requirements

Include only active campaigns launched between January 1 and June 30, 2025.
Calculate total leads, converted leads, and conversion rate for each region and channel. Treat campaigns with no leads as a 0% conversion rate.
Use a window function to rank channels within each region by conversion rate descending. Break ties with total leads descending, then channel name ascending.
Return the region, channel, lead count, conversion count, conversion rate rounded to two decimals, and rank. Sort by region name and rank.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Stanley Martin Homes marketing region name

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
region_id	INT	Region targeted by the campaign
campaign_name	VARCHAR(150)	Campaign name
channel	VARCHAR(50)	Marketing channel
status	VARCHAR(20)	Campaign status
launch_date	DATE	Campaign launch date

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
campaign_id	INT	Attributed campaign identifier
source_detail	VARCHAR(80)	Detailed lead source
created_at	DATE	Lead creation date
converted_at	DATE	Date the lead converted to a qualified opportunity

Tablesregionscampaignsleads