Custom Message Type Counts

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Google

Problem

Which users in chat_msgs received messages carrying a non-standard type, meaning any type other than 'text' or 'image', and how many of each such type did each one receive? For the trust and safety team's review, a message's recipient is the user it replies to, so a message that replies to no one has no recipient.

Not all messages are created equal.

Tables

chat_msgs

Columns: msg_id INTEGER, channel TEXT, sender_id INTEGER, content TEXT, msg_type TEXT, sent_at TEXT, edited INTEGER, reply_to INTEGER

Expected output

Worked solution and explanation

This is an exact-match exclusion on a messy label column, dressed up as a trust and safety report. Everyone finds reply_to and writes the NOT IN. What separates candidates is resisting the urge to tidy msg_type. The table holds 'Text' and 'FILE' next to 'text' and 'file', and a client sending 'FILE' is exactly the kind of non-standard type this report exists to surface. Fold the case and 'Text' quietly passes as a standard message while 'FILE' melts into 'file'. The second quiet bug is the recipient: about three in four messages reply to no one, and a bare grouping on reply_to invents a NULL recipient that holds all of them.

Walking it

Step 1: Read reply_to as the recipient

The table has a sender_id and a reply_to, and only one of them points at the person on the receiving end. Grouping on sender_id answers who sent custom types, which is a different report with the same shape, so it looks right until someone checks a single user.

Step 2: Exclude the two standard labels byte for byte

msg_type NOT IN ('text', 'image') keeps every value that is not literally one of those two strings. That is the point: 'Text' is not 'text', so it stays. Nothing in the seed is spelled 'image' at all, which is fine, because the list states the business rule rather than the data you happen to have today.

Step 3: Drop messages with no recipient

reply_to IS NOT NULL removes the messages that reply to no one. Leave it out and the output gains one extra row keyed on NULL, counting messages nobody received.

Step 4: Count per recipient and literal type

Group on reply_to and msg_type together and COUNT(*) the rows, aliasing the key as recipient_id. Each spelling of a type is its own row, so 'FILE' and 'file' report separately.

SELECT
    reply_to AS recipient_id,
    msg_type,
    COUNT(*) AS msg_count
FROM chat_msgs
WHERE msg_type NOT IN ('text', 'image')
  AND reply_to IS NOT NULL
GROUP BY reply_to, msg_type

Tidied

WHERE LOWER(msg_type) NOT IN ('text', 'image') treats 'Text' as standard and removes it, so the casing violations vanish from a report built to find them.

Exact

WHERE msg_type NOT IN ('text', 'image') keeps every label that is not literally standard, so 'Text' and 'FILE' each surface under their own spelling.

Common follow-up questions

• What if the standard types lived in a `standard_types` table that might hold a `NULL`? (`NOT IN` against a subquery containing `NULL` returns no rows at all; `NOT EXISTS` does not.)
• The team now wants 'FILE' and 'file' reported together, still flagged as a casing violation. How? (Normalize for the grouping key while counting raw spellings that differ from the canonical one.)
• How would you scope this to the last 30 days on a table partitioned by `sent_at`? (Partition pruning: filter on the partition column so only recent partitions are read.)