Average Reply Time in Direct Messages

Hard
441.

Average Reply Time in Direct Messages

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

ColumnType
message_idint
conversation_idint
sender_idint
sent_atdatetime

Primary key: message_id.

One row per direct message. Each conversation has exactly two participants.

Example

The rows must come back in the order shown.

dm_messages

message_idconversation_idsender_idsent_at
110072025-07-01 09:00:00
210082025-07-01 09:04:30
310082025-07-01 09:05:00
410072025-07-01 09:45:10
510192025-07-01 20:00:00
610172025-07-01 22:00:00
710192025-07-01 22:01:59
810282025-07-02 08:00:00

Output

user_idrepliesavg_reply_minutes
911
814
7280
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.

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

ColumnType
message_idint
conversation_idint
sender_idint
sent_atdatetime

Primary key: message_id.

One row per direct message. Each conversation has exactly two participants.

Examples

Example 1

dm_messages

message_idconversation_idsender_idsent_at
110072025-07-01 09:00:00
210082025-07-01 09:04:30
310082025-07-01 09:05:00
410072025-07-01 09:45:10
510192025-07-01 20:00:00
610172025-07-01 22:00:00
710192025-07-01 22:01:59
810282025-07-02 08:00:00

Output

user_idrepliesavg_reply_minutes
911
814
7280

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_id

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