Meta: WhatsApp Business Conversation Windows — SQL Problem
The WhatsApp Business Platform bills by conversation window, and a billing engineer is recomputing the windows from the raw message log.
- Difficulty: Hard
- Topics: Subqueries, Dates, Window Functions
- Dialect: MySQL
- Problem: #766
Problem statement
The WhatsApp Business Platform bills by conversation window, and a billing engineer is recomputing the windows from the raw message log. For each business–customer pair, the first message opens a 24-hour window. Every later message sent less than 24 hours after the window opened belongs to that window; the first message at 24 hours or more after it opened starts a new window, measured from that message. Windows are fixed from their opening message — a message inside a window does not extend it.
Return each business that sent or received at least one message, with the columns business_id and conversations, the total number of windows across all its customers, 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: WaChat
| Column | Type |
|---|---|
| message_id | int |
| business_id | int |
| customer_id | int |
| sent_at | datetime |
Primary key: message_id.
One row per message in either direction between a business and a customer. Two messages of a pair may share a timestamp.
Examples
Example 1
WaChat
| message_id | business_id | customer_id | sent_at |
|---|---|---|---|
| 1 | 1 | 100 | 2025-06-01 09:00:00 |
| 2 | 1 | 100 | 2025-06-01 20:00:00 |
| 3 | 1 | 100 | 2025-06-02 08:59:00 |
| 4 | 1 | 100 | 2025-06-02 09:00:00 |
| 5 | 1 | 100 | 2025-06-03 08:00:00 |
| 6 | 1 | 101 | 2025-06-01 10:00:00 |
| 7 | 1 | 101 | 2025-06-05 10:00:00 |
| 8 | 2 | 100 | 2025-06-01 12:00:00 |
| 9 | 2 | 100 | 2025-06-02 11:00:00 |
| 10 | 2 | 100 | 2025-06-02 13:00:00 |
Output
| business_id | conversations |
|---|---|
| 1 | 4 |
| 2 | 2 |
How to solve Meta: WhatsApp Business Conversation Windows
Most sessionization problems split on the gap between consecutive events, which a LAG handles. Fixed windows are different: whether a message opens a new window depends on when the current window opened, and that depends on the window before it. Messages at 12:00, 11:00 the next day and 13:00 the next day are each less than 24 hours after the previous message, yet the third is 25 hours after the window opened, so there are two windows. A chain like that needs recursion.
The solution recurses on windows rather than messages. The anchor opens one window per pair at its first message (MIN(sent_at)). Each recursive step jumps from a window opened at t straight to the next one: the earliest message of the pair at or after t + 24 hours, guarded by EXISTS so the recursion stops when there is none. Counting the rows per business counts the windows. A message at exactly 24 hours opens a new window because the test is >=; messages sharing a timestamp are harmless since only times matter.
The alternative recurses on messages: number each pair's messages, then walk row rn to rn + 1, carrying the current window's opening time and emitting 1 whenever a message is 24 hours or more past it. It visits every message once per pair, while the jump version takes one step per window; both are linear in the log for these sizes.
Reference solution (MySQL)
WITH RECURSIVE win AS (
SELECT business_id, customer_id, MIN(sent_at) AS opened_at
FROM WaChat
GROUP BY business_id, customer_id
UNION ALL
SELECT w.business_id, w.customer_id,
(SELECT MIN(c.sent_at) FROM WaChat c
WHERE c.business_id = w.business_id AND c.customer_id = w.customer_id
AND c.sent_at >= DATE_ADD(w.opened_at, INTERVAL 24 HOUR))
FROM win w
WHERE EXISTS (SELECT 1 FROM WaChat c
WHERE c.business_id = w.business_id AND c.customer_id = w.customer_id
AND c.sent_at >= DATE_ADD(w.opened_at, INTERVAL 24 HOUR))
)
SELECT business_id, COUNT(*) AS conversations
FROM win
GROUP BY business_idAnother way
WITH RECURSIVE ordered AS (
SELECT business_id, customer_id, sent_at,
ROW_NUMBER() OVER (PARTITION BY business_id, customer_id ORDER BY sent_at) AS rn
FROM WaChat
), walk AS (
SELECT business_id, customer_id, rn, sent_at AS opened_at, 1 AS opens FROM ordered WHERE rn = 1
UNION ALL
SELECT o.business_id, o.customer_id, o.rn,
CASE WHEN o.sent_at >= DATE_ADD(w.opened_at, INTERVAL 24 HOUR) THEN o.sent_at ELSE w.opened_at END,
CASE WHEN o.sent_at >= DATE_ADD(w.opened_at, INTERVAL 24 HOUR) THEN 1 ELSE 0 END
FROM walk w
JOIN ordered o ON o.business_id = w.business_id AND o.customer_id = w.customer_id AND o.rn = w.rn + 1
)
SELECT business_id, SUM(opens) AS conversations
FROM walk
GROUP BY business_id← Meta: Marketplace Listings Against the Same Month Last Year · Meta: Growth Accounting of Monthly Active Users →