Group Chats Silent for More Than 30 Days

Medium
440.

Group Chats Silent for More Than 30 Days

To reduce clutter, the messaging app will suggest archiving group chats that have gone quiet. As of 2025-03-31, a group is quiet when the calendar date of its last message is more than 30 days before 2025-03-31, or when it has no messages at all.

Return the columns chat_id, chat_name and last_message_at (the timestamp of its latest message, NULL if none), ordered by chat_id.

Tables

Table: group_chats

ColumnType
chat_idint
chat_namevarchar
created_ondate

Primary key: chat_id.

One row per group chat.

Table: messages

ColumnType
message_idint
chat_idint
sender_idint
sent_atdatetime

Primary key: message_id.

chat_id is always a row of group_chats. No message is later than 2025-03-31 23:59:59.

Example

The rows must come back in the order shown.

group_chats

chat_idchat_namecreated_on
1Hostel Wing B2024-08-01
2Goa Trip 20252024-12-10
3Cousins2023-05-20
4Placement Prep2025-01-05
5Office Lunch2025-03-28

messages

message_idchat_idsender_idsent_at
11112025-03-30 22:10:00
21122025-02-01 09:00:00
32132025-02-28 23:59:00
43142025-01-12 18:00:00
53152025-01-14 07:45:00
64162025-03-01 00:01:00

Output

chat_idchat_namelast_message_at
2Goa Trip 20252025-02-28 23:59:00
3Cousins2025-01-14 07:45:00
5Office LunchNULL
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.

Group Chats Silent for More Than 30 Days — SQL Joins Problem

To reduce clutter, the messaging app will suggest archiving group chats that have gone quiet.

  • Difficulty: Medium
  • Topics: Joins, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #440

Problem statement

To reduce clutter, the messaging app will suggest archiving group chats that have gone quiet. As of 2025-03-31, a group is quiet when the calendar date of its last message is more than 30 days before 2025-03-31, or when it has no messages at all.

Return the columns chat_id, chat_name and last_message_at (the timestamp of its latest message, NULL if none), ordered by chat_id.

Tables

Table: group_chats

ColumnType
chat_idint
chat_namevarchar
created_ondate

Primary key: chat_id.

One row per group chat.

Table: messages

ColumnType
message_idint
chat_idint
sender_idint
sent_atdatetime

Primary key: message_id.

chat_id is always a row of group_chats. No message is later than 2025-03-31 23:59:59.

Examples

Example 1

group_chats

chat_idchat_namecreated_on
1Hostel Wing B2024-08-01
2Goa Trip 20252024-12-10
3Cousins2023-05-20
4Placement Prep2025-01-05
5Office Lunch2025-03-28

messages

message_idchat_idsender_idsent_at
11112025-03-30 22:10:00
21122025-02-01 09:00:00
32132025-02-28 23:59:00
43142025-01-12 18:00:00
53152025-01-14 07:45:00
64162025-03-01 00:01:00

Output

chat_idchat_namelast_message_at
2Goa Trip 20252025-02-28 23:59:00
3Cousins2025-01-14 07:45:00
5Office LunchNULL

How to solve Group Chats Silent for More Than 30 Days

Each chat's last message is MAX(sent_at) over its messages. A LEFT JOIN from group_chats keeps chats that never had a message, and for them the maximum is NULL. Because the quiet test is about that maximum, it belongs in HAVING: keep a group when the maximum is NULL or when DATEDIFF('2025-03-31', MAX(sent_at)) > 30.

DATEDIFF uses only the date part, so a last message at 00:01 on 1 March is exactly 30 days back and the chat is not quiet, while one at 23:59 on 28 February is 31 days back and is. Seeing that boundary leads to the alternative: a chat is quiet exactly when it has no message on or after 2025-03-01 — a NOT EXISTS that also covers chats with no messages at all — with the last timestamp fetched by a scalar subquery for display.

Grouping by both chat_id and chat_name keeps the query valid under ONLY_FULL_GROUP_BY. With an index on messages(chat_id, sent_at) each chat costs one index probe.

Reference solution (MySQL)

SELECT g.chat_id, g.chat_name, MAX(m.sent_at) AS last_message_at
FROM group_chats g
LEFT JOIN messages m ON m.chat_id = g.chat_id
GROUP BY g.chat_id, g.chat_name
HAVING MAX(m.sent_at) IS NULL OR DATEDIFF('2025-03-31', MAX(m.sent_at)) > 30
ORDER BY g.chat_id

Another way

SELECT g.chat_id, g.chat_name, (SELECT MAX(m.sent_at) FROM messages m WHERE m.chat_id = g.chat_id) AS last_message_at
FROM group_chats g
WHERE NOT EXISTS (SELECT 1 FROM messages m WHERE m.chat_id = g.chat_id AND m.sent_at >= '2025-03-01')
ORDER BY g.chat_id

← Creators Whose Latest Post Underperformed · Average Reply Time in Direct Messages →