Email Campaign Open Rate

Problem

Table: `EmailEvent`

```text
+------------+---------+
| Column | Type |
+------------+---------+
| campaign_id| int |
| subscriber | int |
| outcome | varchar |
+------------+---------+
(campaign_id, subscriber) is the primary key. Each row records what happened
to one email sent to one subscriber in a campaign. outcome is one of
'Opened', 'Delivered', or 'Bounced'.
```

For each campaign, compute its **Open Rate** as a percentage:

```text
open_rate = 0 if no email was Opened or Delivered
open_rate = Opened / (Opened + Delivered) * 100 otherwise
```

Bounced emails count toward neither part of the ratio. Report `campaign_id` and `open_rate`, rounding `open_rate` to two decimal places. Order by `open_rate` descending, then by `campaign_id` ascending.

**Example**

```text
EmailEvent:
+-------------+------------+-----------+
| campaign_id | subscriber | outcome |
+-------------+------------+-----------+
| 1 | 10 | Opened |
| 1 | 11 | Delivered |
| 2 | 10 | Opened |
| 2 | 12 | Opened |
| 3 | 13 | Bounced |
+-------------+------------+-----------+

Output:
+-------------+-----------+
| campaign_id | open_rate |
+-------------+-----------+
| 2 | 100.00 |
| 1 | 50.00 |
| 3 | 0.00 |
+-------------+-----------+
```

Campaign 3 has only a bounce, so its denominator is zero and the rate is 0.00.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns campaign_id, open_rate.

Starter code (SQL)

SELECT *
FROM EmailEvent;

Related SQL questions

• Above-Average CampusesEasy
• Adjacent Open Parking SpotsEasy
• App Ad Click-Through RateEasy
• Asset Labels With a Three-Digit CodeEasy
• Average Listening Sessions per ListenerEasy
• Average Nightly Rate Earned per RoomEasy