Meta: WhatsApp Business Message Delivery Funnel

Medium
759.

Meta: WhatsApp Business Message Delivery Funnel

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

ColumnType
business_idint
business_namevarchar

Primary key: business_id.

One row per business on the platform; names are unique.

Table: WaMessage

ColumnType
message_idint
business_idint
sent_atdatetime
delivered_atdatetime
read_atdatetime

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

WaBusiness

business_idbusiness_name
1Chai Point Express
2Goa Surf School
3Chennai Silk House

WaMessage

message_idbusiness_idsent_atdelivered_atread_at
112025-07-01 09:00:002025-07-01 09:01:002025-07-01 12:00:00
212025-07-01 09:00:002025-07-01 09:02:002025-07-02 09:00:00
312025-07-01 10:00:00NULLNULL
422025-07-02 08:00:00NULL2025-07-02 08:30:00
522025-07-02 08:00:002025-07-02 08:01:002025-07-04 10:00:00
612025-07-03 18:00:002025-07-03 18:00:00NULL

Output

business_namesentdeliveredread_within_day
Chai Point Express432
Goa Surf School221
Chennai Silk House000
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.

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

ColumnType
business_idint
business_namevarchar

Primary key: business_id.

One row per business on the platform; names are unique.

Table: WaMessage

ColumnType
message_idint
business_idint
sent_atdatetime
delivered_atdatetime
read_atdatetime

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_idbusiness_name
1Chai Point Express
2Goa Surf School
3Chennai Silk House

WaMessage

message_idbusiness_idsent_atdelivered_atread_at
112025-07-01 09:00:002025-07-01 09:01:002025-07-01 12:00:00
212025-07-01 09:00:002025-07-01 09:02:002025-07-02 09:00:00
312025-07-01 10:00:00NULLNULL
422025-07-02 08:00:00NULL2025-07-02 08:30:00
522025-07-02 08:00:002025-07-02 08:01:002025-07-04 10:00:00
612025-07-03 18:00:002025-07-03 18:00:00NULL

Output

business_namesentdeliveredread_within_day
Chai Point Express432
Goa Surf School221
Chennai Silk House000

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_name

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