Meta: WhatsApp Business Conversation Windows

Hard
766.

Meta: WhatsApp Business Conversation Windows

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

ColumnType
message_idint
business_idint
customer_idint
sent_atdatetime

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.

Example

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

WaChat

message_idbusiness_idcustomer_idsent_at
111002025-06-01 09:00:00
211002025-06-01 20:00:00
311002025-06-02 08:59:00
411002025-06-02 09:00:00
511002025-06-03 08:00:00
611012025-06-01 10:00:00
711012025-06-05 10:00:00
821002025-06-01 12:00:00
921002025-06-02 11:00:00
1021002025-06-02 13:00:00

Output

business_idconversations
14
22
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 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

ColumnType
message_idint
business_idint
customer_idint
sent_atdatetime

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_idbusiness_idcustomer_idsent_at
111002025-06-01 09:00:00
211002025-06-01 20:00:00
311002025-06-02 08:59:00
411002025-06-02 09:00:00
511002025-06-03 08:00:00
611012025-06-01 10:00:00
711012025-06-05 10:00:00
821002025-06-01 12:00:00
921002025-06-02 11:00:00
1021002025-06-02 13:00:00

Output

business_idconversations
14
22

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_id

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