Email Campaigns That Drove Two or More Unsubscribes
Every email delivered to a subscriber is one row, flagged when the subscriber opened it and when they clicked the unsubscribe link in it.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #277
Problem statement
Every email delivered to a subscriber is one row, flagged when the subscriber opened it and when they clicked the unsubscribe link in it. The deliverability team reviews every campaign that cost the list at least two unsubscribes.
Return email_campaign_id, sends (rows for the campaign) and unsubscribes (rows with unsubscribed = 1) for campaigns with unsubscribes ≥ 2. Order by unsubscribes descending, then email_campaign_id ascending.
Tables
Table: EmailSend
| Column | Type |
|---|---|
| send_id | int |
| email_campaign_id | int |
| subscriber_id | int |
| opened | bool |
| unsubscribed | bool |
Primary key: send_id.
One row per email delivered. opened and unsubscribed are 0 or 1.
Examples
Example 1
EmailSend
| send_id | email_campaign_id | subscriber_id | opened | unsubscribed |
|---|---|---|---|---|
| 1 | 7 | 501 | 1 | 0 |
| 2 | 7 | 502 | 1 | 1 |
| 3 | 7 | 503 | 0 | 0 |
| 4 | 8 | 501 | 1 | 1 |
| 5 | 8 | 504 | 1 | 1 |
| 6 | 8 | 505 | 0 | 0 |
| 7 | 9 | 502 | 1 | 1 |
| 8 | 9 | 506 | 1 | 1 |
| 9 | 9 | 507 | 1 | 0 |
Output
| email_campaign_id | sends | unsubscribes |
|---|---|---|
| 8 | 3 | 2 |
| 9 | 3 | 2 |
How to solve Email Campaigns That Drove Two or More Unsubscribes
Group the sends by campaign. COUNT(*) is the number of emails delivered, and the unsubscribes are a conditional count: SUM(CASE WHEN unsubscribed = 1 THEN 1 ELSE 0 END) adds one per flagged row. Because the flag is already 0 or 1, SUM(unsubscribed) is the same number, and COUNT(IF(…, 1, NULL)) counts the non-NULLs — three spellings of one idea.
The threshold is on an aggregate, so it belongs in HAVING; WHERE unsubscribed = 1 would throw away the other rows before grouping and break the sends column. Ties on the count are ordered by campaign id, so the order is fully fixed. The query is one scan and one hash aggregate.
Reference solution (MySQL)
SELECT email_campaign_id,
COUNT(*) AS sends,
SUM(CASE WHEN unsubscribed = 1 THEN 1 ELSE 0 END) AS unsubscribes
FROM EmailSend
GROUP BY email_campaign_id
HAVING SUM(CASE WHEN unsubscribed = 1 THEN 1 ELSE 0 END) >= 2
ORDER BY unsubscribes DESC, email_campaign_idAnother way
SELECT email_campaign_id, COUNT(*) AS sends, SUM(unsubscribed) AS unsubscribes FROM EmailSend GROUP BY email_campaign_id HAVING SUM(unsubscribed) >= 2 ORDER BY unsubscribes DESC, email_campaign_idAnother way
SELECT email_campaign_id, COUNT(send_id) AS sends, COUNT(IF(unsubscribed = 1, 1, NULL)) AS unsubscribes FROM EmailSend GROUP BY email_campaign_id HAVING COUNT(IF(unsubscribed = 1, 1, NULL)) > 1 ORDER BY unsubscribes DESC, email_campaign_id← Email Subject Lines Cut Off in the Inbox Preview · Instagram Creator Deals Signed in Q3 2024 →