Task
BrightAds wants a simple monthly view of marketing performance from its campaign delivery table. Write a SQL query to summarize campaign results for January 2024.
Requirements
- Return one row per
channel for campaigns with campaign_date in January 2024.
- For each channel, calculate:
- total impressions
- total clicks
- average spend
- Sort the result by
total_clicks in descending order.
Table Definition
marketing_campaigns
| Column | Type | Description |
|---|
| campaign_id | INT | Unique campaign record ID |
| channel | VARCHAR(50) | Marketing channel such as Email or Search |
| campaign_name | VARCHAR(100) | Campaign name |
| campaign_date | DATE | Date the campaign ran |
| impressions | INT | Number of impressions delivered |
| clicks | INT | Number of clicks received |
| spend | DECIMAL(10,2) | Amount spent on the campaign |
| region | VARCHAR(50) | Geographic region |
Sample Data
| campaign_id | channel | campaign_name | campaign_date | impressions | clicks | spend | region |
|---|
| 1 | Social | Winter Promo | 2024-01-15 | 12000 | 480 | 950.00 | North America |
| 2 | Email | New Year Blast | 2024-01-03 | 8000 | 640 | 300.00 | Europe |
| 3 | Search | Brand Terms | 2024-01-20 | 15000 | 750 | 1200.00 | North America |
| 4 | Social | Retargeting Push | 2024-01-08 | 9000 | 360 | 700.00 | Asia Pacific |
| 5 | Display | Awareness Banner | 2024-01-25 | 20000 | 220 | 1500.00 | Europe |
| 6 | Email | Weekend Nudge | 2024-02-02 | 6000 | 420 | 250.00 | North America |
Expected Output
| channel | total_impressions | total_clicks | avg_spend |
|---|
| Search | 15000 | 750 | 1200.00 |
| Email | 12000 | 730 | 250.00 |
| Social | 21000 | 840 | 825.00 |
| Display | 20000 | 220 | 1500.00 |
