Your question is SQL Rank Channels by Region. Start with the requirements and the three 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.
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.
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Unique regional identifier |
| region_name | VARCHAR(100) | Stanley Martin Homes marketing region name |
| 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 |
| 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 |