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
| Column | Type |
|---|---|
| chat_id | int |
| chat_name | varchar |
| created_on | date |
Primary key: chat_id.
One row per group chat.
Table: messages
| Column | Type |
|---|---|
| message_id | int |
| chat_id | int |
| sender_id | int |
| sent_at | datetime |
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_id | chat_name | created_on |
|---|---|---|
| 1 | Hostel Wing B | 2024-08-01 |
| 2 | Goa Trip 2025 | 2024-12-10 |
| 3 | Cousins | 2023-05-20 |
| 4 | Placement Prep | 2025-01-05 |
| 5 | Office Lunch | 2025-03-28 |
messages
| message_id | chat_id | sender_id | sent_at |
|---|---|---|---|
| 1 | 1 | 11 | 2025-03-30 22:10:00 |
| 2 | 1 | 12 | 2025-02-01 09:00:00 |
| 3 | 2 | 13 | 2025-02-28 23:59:00 |
| 4 | 3 | 14 | 2025-01-12 18:00:00 |
| 5 | 3 | 15 | 2025-01-14 07:45:00 |
| 6 | 4 | 16 | 2025-03-01 00:01:00 |
Output
| chat_id | chat_name | last_message_at |
|---|---|---|
| 2 | Goa Trip 2025 | 2025-02-28 23:59:00 |
| 3 | Cousins | 2025-01-14 07:45:00 |
| 5 | Office Lunch | NULL |
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_idAnother 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 →