Meta: WhatsApp Business Message Delivery Funnel
Businesses on the WhatsApp Business Platform see a funnel for the messages they send: sent → delivered → read.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation, Dates
- Dialect: MySQL
- Problem: #759
Problem statement
Businesses on the WhatsApp Business Platform see a funnel for the messages they send: sent → delivered → read. Receipts can go missing — a message that was read was certainly delivered, even if its delivered_at is NULL.
Return one row for every business with the columns business_name, sent (messages sent), delivered (messages with a delivery receipt or a read receipt) and read_within_day (messages read at most 24 hours after sent_at; exactly 24 hours counts). A business that sent nothing shows 0 in every column. Return the rows in any order.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: WaBusiness
| Column | Type |
|---|---|
| business_id | int |
| business_name | varchar |
Primary key: business_id.
One row per business on the platform; names are unique.
Table: WaMessage
| Column | Type |
|---|---|
| message_id | int |
| business_id | int |
| sent_at | datetime |
| delivered_at | datetime |
| read_at | datetime |
Primary key: message_id.
One row per message a business sent. delivered_at and read_at are NULL when that receipt never arrived; a receipt is never earlier than sent_at.
Examples
Example 1
WaBusiness
| business_id | business_name |
|---|---|
| 1 | Chai Point Express |
| 2 | Goa Surf School |
| 3 | Chennai Silk House |
WaMessage
| message_id | business_id | sent_at | delivered_at | read_at |
|---|---|---|---|---|
| 1 | 1 | 2025-07-01 09:00:00 | 2025-07-01 09:01:00 | 2025-07-01 12:00:00 |
| 2 | 1 | 2025-07-01 09:00:00 | 2025-07-01 09:02:00 | 2025-07-02 09:00:00 |
| 3 | 1 | 2025-07-01 10:00:00 | NULL | NULL |
| 4 | 2 | 2025-07-02 08:00:00 | NULL | 2025-07-02 08:30:00 |
| 5 | 2 | 2025-07-02 08:00:00 | 2025-07-02 08:01:00 | 2025-07-04 10:00:00 |
| 6 | 1 | 2025-07-03 18:00:00 | 2025-07-03 18:00:00 | NULL |
Output
| business_name | sent | delivered | read_within_day |
|---|---|---|---|
| Chai Point Express | 4 | 3 | 2 |
| Goa Surf School | 2 | 2 | 1 |
| Chennai Silk House | 0 | 0 | 0 |
How to solve Meta: WhatsApp Business Message Delivery Funnel
Three counts over the same messages, each with its own condition, is conditional aggregation; the LEFT JOIN from WaBusiness keeps the businesses that sent nothing. For those, the single joined row is all NULLs, so COUNT(m.message_id) gives 0 (COUNT(*) would give 1 — the classic mistake), and each CASE evaluates to 0.
Delivery is counted from either receipt, because a read message must have been delivered: delivered_at IS NOT NULL OR read_at IS NOT NULL. The read test compares each message with its own deadline, DATE_ADD(sent_at, INTERVAL 24 HOUR), using <= so a read at exactly 24 hours is in; a NULL read_at makes the comparison unknown, which the CASE sends to ELSE 0.
The alternative aggregates the messages first in a derived table — COUNT(COALESCE(delivered_at, read_at)) counts rows where either receipt exists, and the 24-hour test is a minute difference — then attaches the totals to every business with COALESCE(…, 0) for the missing ones. Both read the messages once.
Reference solution (MySQL)
SELECT b.business_name,
COUNT(m.message_id) AS sent,
SUM(CASE WHEN m.delivered_at IS NOT NULL OR m.read_at IS NOT NULL THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN m.read_at <= DATE_ADD(m.sent_at, INTERVAL 24 HOUR) THEN 1 ELSE 0 END) AS read_within_day
FROM WaBusiness b
LEFT JOIN WaMessage m ON m.business_id = b.business_id
GROUP BY b.business_id, b.business_nameAnother way
SELECT b.business_name, COALESCE(t.sent, 0) AS sent, COALESCE(t.delivered, 0) AS delivered, COALESCE(t.read_within_day, 0) AS read_within_day
FROM WaBusiness b
LEFT JOIN (SELECT business_id, COUNT(*) AS sent, COUNT(COALESCE(delivered_at, read_at)) AS delivered,
COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, sent_at, read_at) <= 1440 THEN 1 END) AS read_within_day
FROM WaMessage GROUP BY business_id) t ON t.business_id = b.business_id← Meta: Facebook Groups Where Every Admin Approved a Post · Meta: Advertisers Whose Spend Fell Three Months Running →