Email Open Rate by Hour of Send

Medium
286.

Email Open Rate by Hour of Send

The CRM team is testing what time of day to send. An email only counts as opened for this test when it was opened within 24 hours of being sent (at most 86,400 seconds after sent_at); later opens and unopened emails count as not opened.

Group the deliveries by the hour of sent_at (0–23). Return send_hour, sends, opens and open_rate_pct = 100 × opens ÷ sends rounded to 2 decimals, for every hour that has at least one send, ordered by send_hour.

Tables

Table: EmailDelivery

ColumnType
delivery_idint
subscriber_idint
sent_atdatetime
opened_atdatetime

Primary key: delivery_id.

One row per email delivered; opened_at is the first open, or NULL if it was never opened.

Example

The rows must come back in the order shown.

EmailDelivery

delivery_idsubscriber_idsent_atopened_at
15012024-12-02 09:00:002024-12-02 09:41:12
25022024-12-02 09:00:00NULL
35032024-12-02 09:30:002024-12-03 09:30:00
45042024-12-02 19:00:002024-12-04 08:00:00
55052024-12-02 19:15:002024-12-02 22:02:45
65062024-12-03 19:00:002024-12-03 19:05:00
75072024-12-03 07:00:00NULL

Output

send_hoursendsopensopen_rate_pct
7100
93266.67
193266.67
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 Open Rate by Hour of Send — SQL Dates Problem

The CRM team is testing what time of day to send. An email only counts as opened for this test when it was opened within 24 hours of being sent (at most…

  • Difficulty: Medium
  • Topics: Dates, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #286

Problem statement

The CRM team is testing what time of day to send. An email only counts as opened for this test when it was opened within 24 hours of being sent (at most 86,400 seconds after sent_at); later opens and unopened emails count as not opened.

Group the deliveries by the hour of sent_at (0–23). Return send_hour, sends, opens and open_rate_pct = 100 × opens ÷ sends rounded to 2 decimals, for every hour that has at least one send, ordered by send_hour.

Tables

Table: EmailDelivery

ColumnType
delivery_idint
subscriber_idint
sent_atdatetime
opened_atdatetime

Primary key: delivery_id.

One row per email delivered; opened_at is the first open, or NULL if it was never opened.

Examples

Example 1

EmailDelivery

delivery_idsubscriber_idsent_atopened_at
15012024-12-02 09:00:002024-12-02 09:41:12
25022024-12-02 09:00:00NULL
35032024-12-02 09:30:002024-12-03 09:30:00
45042024-12-02 19:00:002024-12-04 08:00:00
55052024-12-02 19:15:002024-12-02 22:02:45
65062024-12-03 19:00:002024-12-03 19:05:00
75072024-12-03 07:00:00NULL

Output

send_hoursendsopensopen_rate_pct
7100
93266.67
193266.67

How to solve Email Open Rate by Hour of Send

Bucket by HOUR(sent_at) and count two things per bucket: all deliveries, and the deliveries that count as opened. The second is a conditional sum — 1 when opened_at exists and TIMESTAMPDIFF(SECOND, sent_at, opened_at) <= 86400, else 0. An email opened exactly 24 hours later still counts; one second later does not.

NULL needs no special case in the alternative: opened_at <= DATE_ADD(sent_at, INTERVAL 1 DAY) is unknown for a NULL opened_at, and IF treats unknown as false, giving 0. Comparing to the send time plus one day is the same window as the seconds difference.

The rate is computed from the two exact counts and rounded last. Every hour in the output has at least one send, so the division is always defined. One scan with a 24-bucket aggregate.

Reference solution (MySQL)

SELECT HOUR(sent_at) AS send_hour,
       COUNT(*) AS sends,
       SUM(CASE WHEN opened_at IS NOT NULL AND TIMESTAMPDIFF(SECOND, sent_at, opened_at) <= 86400 THEN 1 ELSE 0 END) AS opens,
       ROUND(100 * SUM(CASE WHEN opened_at IS NOT NULL AND TIMESTAMPDIFF(SECOND, sent_at, opened_at) <= 86400 THEN 1 ELSE 0 END) / COUNT(*), 2) AS open_rate_pct
FROM EmailDelivery
GROUP BY HOUR(sent_at)
ORDER BY send_hour

Another way

SELECT send_hour, COUNT(*) AS sends, SUM(o) AS opens, ROUND(SUM(o) * 100 / COUNT(*), 2) AS open_rate_pct FROM (
  SELECT HOUR(sent_at) AS send_hour, IF(opened_at <= DATE_ADD(sent_at, INTERVAL 1 DAY), 1, 0) AS o FROM EmailDelivery
) d GROUP BY send_hour ORDER BY send_hour

← Last-Touch Channel for Each Conversion · Campaigns Outspending Their Channel's Average →