Email Subscribers Who Open but Never Click

Medium
290.

Email Subscribers Who Open but Never Click

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

ColumnType
subscriber_idint
emailvarchar
cityvarchar

Primary key: subscriber_id.

One row per newsletter subscriber.

Table: EmailEvent

ColumnTypeValues
event_idint
subscriber_idint
email_campaign_idint
event_typeenumdelivered, open, click, unsubscribe
event_atdatetime

Primary key: event_id.

One row per tracked event; opening the same email twice gives two open rows.

Example

The rows must come back in the order shown.

Subscriber

subscriber_idemailcity
501aditi@mail.inPune
502farhan@mail.inMumbai
503tanvi@mail.inKochi
504noah@mail.inDelhi

EmailEvent

event_idsubscriber_idemail_campaign_idevent_typeevent_at
15011open2024-05-01 08:00:00
25012open2024-05-08 08:10:00
35013open2024-05-15 07:55:00
45021open2024-05-01 09:00:00
55021open2024-05-02 09:00:00
65022open2024-05-08 09:00:00
75022delivered2024-05-08 06:00:00
85031open2024-05-01 10:00:00
95032open2024-05-08 10:00:00
105033open2024-05-15 10:00:00
1 more row

Output

subscriber_idemailcampaigns_opened
501aditi@mail.in3
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 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

ColumnType
subscriber_idint
emailvarchar
cityvarchar

Primary key: subscriber_id.

One row per newsletter subscriber.

Table: EmailEvent

ColumnType
event_idint
subscriber_idint
email_campaign_idint
event_typeenum(delivered, open, click, unsubscribe)
event_atdatetime

Primary key: event_id.

One row per tracked event; opening the same email twice gives two open rows.

Examples

Example 1

Subscriber

subscriber_idemailcity
501aditi@mail.inPune
502farhan@mail.inMumbai
503tanvi@mail.inKochi
504noah@mail.inDelhi

EmailEvent

event_idsubscriber_idemail_campaign_idevent_typeevent_at
15011open2024-05-01 08:00:00
25012open2024-05-08 08:10:00
35013open2024-05-15 07:55:00
45021open2024-05-01 09:00:00
55021open2024-05-02 09:00:00
65022open2024-05-08 09:00:00
75022delivered2024-05-08 06:00:00
85031open2024-05-01 10:00:00
95032open2024-05-08 10:00:00
105033open2024-05-15 10:00:00
115033click2024-05-15 10:01:00

Output

subscriber_idemailcampaigns_opened
501aditi@mail.in3

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_id

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