Email Subscribers Who Open but Never Click — SQL Problem
The lifecycle team wants a re-engagement list: subscribers who keep opening the newsletters but never click through.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation, Joins
- Dialect: MySQL
- Problem: #290
Problem statement
The lifecycle team wants a re-engagement list: subscribers who keep opening the newsletters but never click through. An open or a click is an event of that type; delivery and unsubscribe events don't matter here.
Return subscriber_id, email and campaigns_opened (the number of distinct email campaigns the subscriber opened) for subscribers who opened at least 3 distinct campaigns and have no click event at all. Order by campaigns_opened descending, then subscriber_id.
Tables
Table: Subscriber
| Column | Type |
|---|---|
| subscriber_id | int |
| varchar | |
| city | varchar |
Primary key: subscriber_id.
One row per newsletter subscriber.
Table: EmailEvent
| Column | Type |
|---|---|
| event_id | int |
| subscriber_id | int |
| email_campaign_id | int |
| event_type | enum(delivered, open, click, unsubscribe) |
| event_at | datetime |
Primary key: event_id.
One row per tracked event; opening the same email twice gives two open rows.
Examples
Example 1
Subscriber
| subscriber_id | city | |
|---|---|---|
| 501 | aditi@mail.in | Pune |
| 502 | farhan@mail.in | Mumbai |
| 503 | tanvi@mail.in | Kochi |
| 504 | noah@mail.in | Delhi |
EmailEvent
| event_id | subscriber_id | email_campaign_id | event_type | event_at |
|---|---|---|---|---|
| 1 | 501 | 1 | open | 2024-05-01 08:00:00 |
| 2 | 501 | 2 | open | 2024-05-08 08:10:00 |
| 3 | 501 | 3 | open | 2024-05-15 07:55:00 |
| 4 | 502 | 1 | open | 2024-05-01 09:00:00 |
| 5 | 502 | 1 | open | 2024-05-02 09:00:00 |
| 6 | 502 | 2 | open | 2024-05-08 09:00:00 |
| 7 | 502 | 2 | delivered | 2024-05-08 06:00:00 |
| 8 | 503 | 1 | open | 2024-05-01 10:00:00 |
| 9 | 503 | 2 | open | 2024-05-08 10:00:00 |
| 10 | 503 | 3 | open | 2024-05-15 10:00:00 |
| 11 | 503 | 3 | click | 2024-05-15 10:01:00 |
Output
| subscriber_id | campaigns_opened | |
|---|---|---|
| 501 | aditi@mail.in | 3 |
How to solve Email Subscribers Who Open but Never Click
Group each subscriber's events and compute two numbers from the same group with conditional aggregation. The opened campaigns are COUNT(DISTINCT CASE WHEN event_type = 'open' THEN email_campaign_id END): for any other event type the CASE yields NULL, which COUNT ignores, and DISTINCT collapses repeat opens of one email. The clicks are SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END). HAVING keeps the groups with at least three campaigns and zero clicks.
The alternative splits the two conditions: join only the open events (so the plain distinct count works) and exclude clickers with NOT EXISTS. Filtering opens in the join would make a conditional click count impossible, which is why the click check moves to a subquery there.
Both read the events once (plus a probe per subscriber for the anti join); an index on (subscriber_id, event_type) helps the second.
Reference solution (MySQL)
SELECT s.subscriber_id, s.email,
COUNT(DISTINCT CASE WHEN e.event_type = 'open' THEN e.email_campaign_id END) AS campaigns_opened
FROM Subscriber s
JOIN EmailEvent e ON e.subscriber_id = s.subscriber_id
GROUP BY s.subscriber_id, s.email
HAVING COUNT(DISTINCT CASE WHEN e.event_type = 'open' THEN e.email_campaign_id END) >= 3
AND SUM(CASE WHEN e.event_type = 'click' THEN 1 ELSE 0 END) = 0
ORDER BY campaigns_opened DESC, s.subscriber_idAnother way
SELECT s.subscriber_id, s.email, COUNT(DISTINCT e.email_campaign_id) AS campaigns_opened
FROM Subscriber s JOIN EmailEvent e ON e.subscriber_id = s.subscriber_id AND e.event_type = 'open'
WHERE NOT EXISTS (SELECT 1 FROM EmailEvent c WHERE c.subscriber_id = s.subscriber_id AND c.event_type = 'click')
GROUP BY s.subscriber_id, s.email
HAVING COUNT(DISTINCT e.email_campaign_id) >= 3
ORDER BY campaigns_opened DESC, s.subscriber_id← Month-over-Month Ad Spend Change by Channel · First-Touch vs Last-Touch Revenue by Channel →