Meta-style Company ChallengeEasyVerified answerSQLite live

Unread Direct Messages

Which users have unread direct messages, and how many?

  • Joins
  • Aggregation
  • NULL handling
  • Filtering
  • Sorting

Challenge brief

Understand the request

Messenger Team is investigating notification delivery issues and needs to see which users have unread messages in their inbox.

Show each receiver who has at least one unread message with their unread count.

Return

  • receiver_username
  • unread_message_count

Constraints

  • Treat a message as unread when read_at is null
  • Show the largest unread totals first and resolve ties by receiver username

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

messages

  • message_idINTEGER
  • receiver_idINTEGER
  • read_atDATETIME

users

  • user_idINTEGER
  • usernameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Messages are in messages. The read_at column is NULL when unread. You need to count unread messages per receiver and look up their username from users.

Hint 2

INNER JOIN messages to users on receiver_id = user_id. WHERE m.read_at IS NULL. GROUP BY receiver_id. COUNT(m.message_id) AS unread_message_count.

Hint 3

Start with the tables that establish the result grain for question 19, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT u.username AS receiver_username, COUNT(m.message_id) AS unread_message_count FROM messages m INNER JOIN users u ON m.receiver_id = u.user_id WHERE m.read_at IS NULL GROUP BY m.receiver_id, u.username ORDER BY unread_message_count DESC, receiver_username ASC

Why this works

read_at IS NULL identifies unread messages. INNER JOIN brings in receiver username. GROUP BY receiver gives the per-user count. Only sarah_jones has an unread message (message_id 3 from mike_wilson).

Success check

1 user — sarah_jones has 1 unread message

Expected result

Use this output to verify values, aliases, ordering, and row count.

receiver_usernameunread_message_count
sarah_jones1

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.