Email Campaigns That Drove Two or More Unsubscribes

Easy
277.

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. 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

ColumnType
send_idint
email_campaign_idint
subscriber_idint
openedbool
unsubscribedbool

Primary key: send_id.

One row per email delivered. opened and unsubscribed are 0 or 1.

Example

The rows must come back in the order shown.

EmailSend

send_idemail_campaign_idsubscriber_idopenedunsubscribed
1750110
2750211
3750300
4850111
5850411
6850500
7950211
8950611
9950710

Output

email_campaign_idsendsunsubscribes
832
932
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
send_idint
email_campaign_idint
subscriber_idint
openedbool
unsubscribedbool

Primary key: send_id.

One row per email delivered. opened and unsubscribed are 0 or 1.

Examples

Example 1

EmailSend

send_idemail_campaign_idsubscriber_idopenedunsubscribed
1750110
2750211
3750300
4850111
5850411
6850500
7950211
8950611
9950710

Output

email_campaign_idsendsunsubscribes
832
932

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_id

Another 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_id

Another 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 →