Top Marketing Channel by Season

Problem

Table: `assets`

```text
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| asset_id | int |
| asset_name | varchar |
| channel | varchar |
+--------------+---------+
asset_id is the primary key for this table.
```

Table: `spots`

```text
+-------------+---------------+
| Column Name | Type |
+-------------+---------------+
| spot_id | int |
| asset_id | int |
| run_date | date |
| clicks | int |
| cpc | decimal(10,2) |
+-------------+---------------+
spot_id is the primary key for this table. cpc is the cost per click.
```

Each ad spot belongs to an asset, and each asset belongs to a marketing channel. Group the calendar into four seasons by month: Winter (12, 1, 2), Spring (3, 4, 5), Summer (6, 7, 8), Fall (9, 10, 11).

For each season, find the single channel with the most total clicks. If two channels tie on clicks, prefer the one with the higher total spend (clicks × cpc); if still tied, prefer the alphabetically earlier channel name.

Return `season`, `channel`, `total_clicks`, and `total_spend`, ordered by `season`.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 4 rows with the columns season, channel, total_clicks, total_spend.

Starter code (SQL)

SELECT *
FROM assets;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium