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
| Column | Type |
|---|---|
| delivery_id | int |
| subscriber_id | int |
| sent_at | datetime |
| opened_at | datetime |
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_id | subscriber_id | sent_at | opened_at |
|---|---|---|---|
| 1 | 501 | 2024-12-02 09:00:00 | 2024-12-02 09:41:12 |
| 2 | 502 | 2024-12-02 09:00:00 | NULL |
| 3 | 503 | 2024-12-02 09:30:00 | 2024-12-03 09:30:00 |
| 4 | 504 | 2024-12-02 19:00:00 | 2024-12-04 08:00:00 |
| 5 | 505 | 2024-12-02 19:15:00 | 2024-12-02 22:02:45 |
| 6 | 506 | 2024-12-03 19:00:00 | 2024-12-03 19:05:00 |
| 7 | 507 | 2024-12-03 07:00:00 | NULL |
Output
| send_hour | sends | opens | open_rate_pct |
|---|---|---|---|
| 7 | 1 | 0 | 0 |
| 9 | 3 | 2 | 66.67 |
| 19 | 3 | 2 | 66.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_hourAnother 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 →