Your question is Top Local Campaigns Per 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.
Google Ads regional marketing teams need to compare local campaigns by return on ad spend, or ROAS. Write a PostgreSQL query that ranks eligible local campaigns within each region and preserves ties at the third rank.
status = 'active' and metrics from January 2025.| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Unique region identifier |
| region_name | VARCHAR(100) | Google Ads reporting region |
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| region_id | INT | Region targeted by the campaign |
| campaign_name | VARCHAR(150) | Campaign display name |
| is_local | BOOLEAN | Whether the campaign targets a local market |
| status | VARCHAR(20) | Campaign lifecycle status |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique daily metric record |
| campaign_id | INT | Measured campaign |
| metric_date | DATE | Date of the recorded metrics |
| impressions | INT | Number of ad impressions |
| clicks | INT | Number of ad clicks |
| conversions | INT | Attributed conversions |
| spend | NUMERIC(12,2) | Advertising spend |
| revenue | NUMERIC(12,2) | Attributed revenue |