Monthly Ad Performance by Region

Problem

Table: `Clicks`

```text
+-----------+---------+
| Column | Type |
+-----------+---------+
| click_id | int |
| region | varchar |
| outcome | varchar |
| spend | int |
| click_day | date |
+-----------+---------+
click_id is the primary key. outcome is 'converted' or 'bounced'. spend is the
cost of the click in cents.
```

For each month and region, report the total number of clicks and their total
spend, plus the number of **converted** clicks and their total spend. The
month must be formatted as `YYYY-MM`. Output columns, in this order:
`month`, `region`, `total_clicks`, `converted_clicks`, `total_spend`,
`converted_spend`.

**Example**

```text
Clicks:
+----------+--------+-----------+-------+------------+
| click_id | region | outcome | spend | click_day |
+----------+--------+-----------+-------+------------+
| 1 | EU | converted | 30 | 2024-01-05 |
| 2 | EU | bounced | 20 | 2024-01-20 |
| 3 | US | converted | 40 | 2024-01-08 |
+----------+--------+-----------+-------+------------+

Output:
+---------+--------+--------------+------------------+-------------+-----------------+
| month | region | total_clicks | converted_clicks | total_spend | converted_spend |
+---------+--------+--------------+------------------+-------------+-----------------+
| 2024-01 | EU | 2 | 1 | 50 | 30 |
| 2024-01 | US | 1 | 1 | 40 | 40 |
+---------+--------+--------------+------------------+-------------+-----------------+
```

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 2 rows with the columns month, region, total_clicks, converted_clicks, total_spend, converted_spend.

Starter code (SQL)

SELECT *
FROM Clicks;

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