Google: Median First-Reply Time in Gmail Support Threads
Google's support teams answer customers from shared Gmail inboxes. A team lead wants each team's median first-reply time.
- Difficulty: Hard
- Topics: Window Functions, Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #696
Problem statement
Google's support teams answer customers from shared Gmail inboxes. A team lead wants each team's median first-reply time.
A thread's opening message is its earliest message; its first reply is the earliest later message sent by someone other than the opener (follow-ups from the opener do not count). The reply time is TIMESTAMPDIFF(MINUTE, opening, first reply). Considering only threads that received a reply, return team, replied_threads and median_reply_minutes — the middle reply time, or the average of the two middle ones when the count is even. Teams with no replied thread are left out. Order by team.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: SupportThread
| Column | Type |
|---|---|
| thread_id | int |
| team | varchar |
Primary key: thread_id.
One customer conversation and the team whose inbox it landed in.
Table: ThreadMessage
| Column | Type |
|---|---|
| message_id | int |
| thread_id | int |
| sender | varchar |
| sent_at | datetime |
Primary key: message_id.
One email in a thread. No two messages of a thread share a sent_at; message ids are not in time order.
Examples
Example 1
SupportThread
| thread_id | team |
|---|---|
| 1 | Workspace Billing |
| 2 | Workspace Billing |
| 3 | Workspace Billing |
| 4 | Workspace Billing |
| 5 | Account Recovery |
| 6 | Account Recovery |
ThreadMessage
| message_id | thread_id | sender | sent_at |
|---|---|---|---|
| 17 | 1 | asha@zentrix.io | 2025-06-02 10:00:00 |
| 12 | 1 | asha@zentrix.io | 2025-06-02 10:20:00 |
| 15 | 1 | agent.ravi | 2025-06-02 10:45:30 |
| 11 | 2 | kabir@quillpad.in | 2025-06-02 09:00:00 |
| 14 | 2 | agent.meera | 2025-06-02 09:10:00 |
| 13 | 3 | zara@tiffinly.co | 2025-06-02 08:00:00 |
| 16 | 3 | agent.ravi | 2025-06-02 10:00:00 |
| 18 | 4 | dev@ledgerly.com | 2025-06-02 11:00:00 |
| 21 | 5 | neha@gmail.com | 2025-06-03 12:00:00 |
| 24 | 5 | agent.tanvi | 2025-06-03 12:30:00 |
| 22 | 5 | agent.dev | 2025-06-03 12:35:00 |
| 23 | 6 | rohan@gmail.com | 2025-06-03 13:00:00 |
| 20 | 6 | agent.tanvi | 2025-06-03 13:50:00 |
Output
| team | replied_threads | median_reply_minutes |
|---|---|---|
| Account Recovery | 2 | 40 |
| Workspace Billing | 3 | 45 |
How to solve Google: Median First-Reply Time in Gmail Support Threads
Three steps. Opening message: ROW_NUMBER() by sent_at per thread marks the earliest message; its sender is the opener. Message ids are deliberately out of order, so ordering by id would pick the wrong opener.
First reply: join the opening to the thread's later messages from a different sender and take MIN(sent_at). The opener's own follow-ups fail the sender test, so a customer who writes twice before anyone answers does not count as a reply. An inner join drops threads nobody answered, which keeps them out of the median. TIMESTAMPDIFF(MINUTE, …) gives whole minutes.
Median: number each team's reply times in ascending order with a count of the team's rows alongside. The middle positions are FLOOR((cnt + 1) / 2) and FLOOR((cnt + 2) / 2) — the same position when cnt is odd, the two middle ones when it is even — and AVG over the selected rows is the median either way. Ties in reply times do not matter, because positions are unique.
The alternative avoids windows: the opener via NOT EXISTS, the reply via a correlated MIN, and the median as the values with at least half the team at or below and at least half at or above, averaged with AVG(DISTINCT …) (duplicates of a middle value must not be weighted twice).
Reference solution (MySQL)
WITH ordered AS (
SELECT thread_id, sender, sent_at,
ROW_NUMBER() OVER (PARTITION BY thread_id ORDER BY sent_at) AS rn
FROM ThreadMessage
), opening AS (
SELECT thread_id, sender, sent_at FROM ordered WHERE rn = 1
), replies AS (
SELECT o.thread_id,
TIMESTAMPDIFF(MINUTE, o.sent_at, MIN(m.sent_at)) AS mins
FROM opening o
JOIN ThreadMessage m
ON m.thread_id = o.thread_id AND m.sent_at > o.sent_at AND m.sender <> o.sender
GROUP BY o.thread_id, o.sent_at
), ranked AS (
SELECT t.team, r.mins,
ROW_NUMBER() OVER (PARTITION BY t.team ORDER BY r.mins) AS pos,
COUNT(*) OVER (PARTITION BY t.team) AS cnt
FROM replies r
JOIN SupportThread t ON t.thread_id = r.thread_id
)
SELECT team, MAX(cnt) AS replied_threads, AVG(mins) AS median_reply_minutes
FROM ranked
WHERE pos IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY team
ORDER BY teamAnother way
WITH opening AS (SELECT m.thread_id, m.sender, m.sent_at FROM ThreadMessage m WHERE NOT EXISTS (SELECT 1 FROM ThreadMessage e WHERE e.thread_id = m.thread_id AND e.sent_at < m.sent_at)), replies AS (SELECT t.team, TIMESTAMPDIFF(MINUTE, o.sent_at, (SELECT MIN(r.sent_at) FROM ThreadMessage r WHERE r.thread_id = o.thread_id AND r.sent_at > o.sent_at AND r.sender <> o.sender)) AS mins FROM opening o JOIN SupportThread t ON t.thread_id = o.thread_id), done AS (SELECT team, mins FROM replies WHERE mins IS NOT NULL) SELECT x.team, (SELECT COUNT(*) FROM done c WHERE c.team = x.team) AS replied_threads, AVG(DISTINCT x.mins) AS median_reply_minutes FROM done x WHERE 2 * (SELECT COUNT(*) FROM done y WHERE y.team = x.team AND y.mins <= x.mins) >= (SELECT COUNT(*) FROM done c WHERE c.team = x.team) AND 2 * (SELECT COUNT(*) FROM done y WHERE y.team = x.team AND y.mins >= x.mins) >= (SELECT COUNT(*) FROM done c WHERE c.team = x.team) GROUP BY x.team ORDER BY x.team← Google: Longest YouTube Autoplay Chain in Each Session · Google: Search Experiment Lift Without Contaminated Users →