Google: Median First-Reply Time in Gmail Support Threads

Hard
696.

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.

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

ColumnType
thread_idint
teamvarchar

Primary key: thread_id.

One customer conversation and the team whose inbox it landed in.

Table: ThreadMessage

ColumnType
message_idint
thread_idint
sendervarchar
sent_atdatetime

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.

Example

The rows must come back in the order shown.

SupportThread

thread_idteam
1Workspace Billing
2Workspace Billing
3Workspace Billing
4Workspace Billing
5Account Recovery
6Account Recovery

ThreadMessage

message_idthread_idsendersent_at
171asha@zentrix.io2025-06-02 10:00:00
121asha@zentrix.io2025-06-02 10:20:00
151agent.ravi2025-06-02 10:45:30
112kabir@quillpad.in2025-06-02 09:00:00
142agent.meera2025-06-02 09:10:00
133zara@tiffinly.co2025-06-02 08:00:00
163agent.ravi2025-06-02 10:00:00
184dev@ledgerly.com2025-06-02 11:00:00
215neha@gmail.com2025-06-03 12:00:00
245agent.tanvi2025-06-03 12:30:00
3 more rows

Output

teamreplied_threadsmedian_reply_minutes
Account Recovery240
Workspace Billing345
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.

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

ColumnType
thread_idint
teamvarchar

Primary key: thread_id.

One customer conversation and the team whose inbox it landed in.

Table: ThreadMessage

ColumnType
message_idint
thread_idint
sendervarchar
sent_atdatetime

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_idteam
1Workspace Billing
2Workspace Billing
3Workspace Billing
4Workspace Billing
5Account Recovery
6Account Recovery

ThreadMessage

message_idthread_idsendersent_at
171asha@zentrix.io2025-06-02 10:00:00
121asha@zentrix.io2025-06-02 10:20:00
151agent.ravi2025-06-02 10:45:30
112kabir@quillpad.in2025-06-02 09:00:00
142agent.meera2025-06-02 09:10:00
133zara@tiffinly.co2025-06-02 08:00:00
163agent.ravi2025-06-02 10:00:00
184dev@ledgerly.com2025-06-02 11:00:00
215neha@gmail.com2025-06-03 12:00:00
245agent.tanvi2025-06-03 12:30:00
225agent.dev2025-06-03 12:35:00
236rohan@gmail.com2025-06-03 13:00:00
206agent.tanvi2025-06-03 13:50:00

Output

teamreplied_threadsmedian_reply_minutes
Account Recovery240
Workspace Billing345

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 team

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