Can you write a SQL query using joins, subqueries, and group by to analyze marketing database performance?
Use the provided marketing tables for the January 2025 reporting period. Include active campaigns whose dates overlap January 2025, including campaigns with no matching activity.
campaign_id, campaign_name, channel, total_spend, impressions, clicks, conversions, revenue, ctr_percent, conversion_rate_percent, roas, channel_avg_spend, and roas_rank.channel, roas_rank, and campaign_id.| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Unique campaign identifier |
| campaign_name | VARCHAR(100) | Campaign display name |
| channel | VARCHAR(50) | Marketing channel |
| start_date | DATE | Campaign start date |
| end_date | DATE | Campaign end date |
| budget | DECIMAL(12,2) | Allocated campaign budget |
| status | VARCHAR(20) | Campaign lifecycle status |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique metric record identifier |
| campaign_id | INT | Referenced campaign |
| metric_date | DATE | Date of measured activity |
| impressions | INT | Ad impressions |
| clicks | INT | Ad clicks |
| spend | DECIMAL(12,2) | Spend recorded for the metric date |
| Column | Type | Description |
|---|---|---|
| conversion_idPK | INT | Unique conversion record identifier |
| campaign_id | INT | Campaign credited with the conversions |
| conversion_date | DATE | Date of attributed conversions |
| conversions | INT | Number of conversions |
| revenue | DECIMAL(12,2) | Revenue attributed to conversions |