Average Reply Time in Direct Messages — SQL Problem
The messaging team measures how quickly people answer DMs. Every conversation is between exactly two users.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #441
Problem statement
The messaging team measures how quickly people answer DMs. Every conversation is between exactly two users. Order a conversation's messages by sent_at, then message_id. A message is a reply when the message just before it in the same conversation was sent by the other person; its reply time is the number of whole minutes between the two messages (TIMESTAMPDIFF(MINUTE, …)). A second message in a row from the same sender is not a reply, and a conversation's first message is not a reply.
Return the columns user_id (the replier), replies and avg_reply_minutes (the average reply time, rounded to 1 decimal) for every user with at least one reply, ordered by avg_reply_minutes, then user_id.
Tables
Table: dm_messages
| Column | Type |
|---|---|
| message_id | int |
| conversation_id | int |
| sender_id | int |
| sent_at | datetime |
Primary key: message_id.
One row per direct message. Each conversation has exactly two participants.
Examples
Example 1
dm_messages
| message_id | conversation_id | sender_id | sent_at |
|---|---|---|---|
| 1 | 100 | 7 | 2025-07-01 09:00:00 |
| 2 | 100 | 8 | 2025-07-01 09:04:30 |
| 3 | 100 | 8 | 2025-07-01 09:05:00 |
| 4 | 100 | 7 | 2025-07-01 09:45:10 |
| 5 | 101 | 9 | 2025-07-01 20:00:00 |
| 6 | 101 | 7 | 2025-07-01 22:00:00 |
| 7 | 101 | 9 | 2025-07-01 22:01:59 |
| 8 | 102 | 8 | 2025-07-02 08:00:00 |
Output
| user_id | replies | avg_reply_minutes |
|---|---|---|
| 9 | 1 | 1 |
| 8 | 1 | 4 |
| 7 | 2 | 80 |
How to solve Average Reply Time in Direct Messages
The definition of a reply looks one row back, so the tool is LAG. Over a window partitioned by conversation and ordered by sent_at, message_id, LAG(sender_id) and LAG(sent_at) put the previous message's sender and time beside each message. The partition matters: without it, the first message of one conversation would look back into another.
A row is a reply when prev_sender is not NULL (it is not the conversation's first message) and differs from sender_id (a double text is not an answer). For those rows the reply time is TIMESTAMPDIFF(MINUTE, prev_sent_at, sent_at) — whole minutes, so 1 minute 59 seconds counts as 1. The outer query groups by the replier, counts replies and averages, rounded to one decimal so every engine prints the same value.
Without window functions, a correlated subquery finds each message's predecessor — the latest earlier message by (sent_at, message_id) — and a join brings its sender and time. It is correct but runs one ordered lookup per message, against one sort per conversation for the window version. The final order is by the rounded average, with user_id breaking ties.
Reference solution (MySQL)
WITH ordered AS (
SELECT sender_id, sent_at,
LAG(sender_id) OVER (PARTITION BY conversation_id ORDER BY sent_at, message_id) AS prev_sender,
LAG(sent_at) OVER (PARTITION BY conversation_id ORDER BY sent_at, message_id) AS prev_sent_at
FROM dm_messages
)
SELECT sender_id AS user_id,
COUNT(*) AS replies,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, prev_sent_at, sent_at)), 1) AS avg_reply_minutes
FROM ordered
WHERE prev_sender IS NOT NULL AND prev_sender <> sender_id
GROUP BY sender_id
ORDER BY avg_reply_minutes, user_idAnother way
SELECT d.sender_id AS user_id, COUNT(*) AS replies,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, p.sent_at, d.sent_at)), 1) AS avg_reply_minutes
FROM dm_messages d
JOIN dm_messages p ON p.message_id = (
SELECT q.message_id FROM dm_messages q
WHERE q.conversation_id = d.conversation_id
AND (q.sent_at < d.sent_at OR (q.sent_at = d.sent_at AND q.message_id < d.message_id))
ORDER BY q.sent_at DESC, q.message_id DESC LIMIT 1)
WHERE p.sender_id <> d.sender_id
GROUP BY d.sender_id
ORDER BY avg_reply_minutes, user_id← Group Chats Silent for More Than 30 Days · Friend Suggestions From Two or More Mutual Friends →