Frequent Message Senders

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

Amazon

Problem

Our anomaly-detection team is hunting accounts that flooded the chat platform this year, where only messages carrying actual text count as real activity (blank posts are just noise). Surface every sender behind more than two such messages, along with that count.

Someone is sending too many messages.

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

What this is really asking

This is a two-phase filter wearing an anomaly-detection costume. You filter rows FIRST (keep only this year's messages that carry text), collapse them to one row per sender_id, count what survived, then filter the GROUPS down to those above two. A single WHERE cannot say 'more than two surviving messages' because that count does not exist until the rows are grouped. The trap is knowing which filter runs at which phase: mix them up and you either crash or count the wrong rows.

Break it down

Step 1: Filter the rows in WHERE

content <> '' drops empty and null content (in SQL, NULL <> '' is not true, so those rows fall out too), and sent_at >= the start of the year drops older messages. This runs before grouping, so only qualifying rows ever reach the count.

Step 2: Aggregate per sender

GROUP BY sender_id with COUNT(*) yields one row per sender carrying their surviving message total across every channel. It is COUNT(*), not COUNT(content): after the WHERE there are no nulls left, and every remaining row is one real message.

Step 3: Filter the groups in HAVING

The more-than-two test reads COUNT(*), which does not exist until the group forms, so it belongs in HAVING. A sender with only one or two qualifying messages this year is dropped here, leaving just the accounts that are actually flooding.

Step 4: Order busiest first

ORDER BY msg_count DESC puts the loudest senders first. Counts tie (many senders share the same total), so sender_id ascending is the tiebreaker that makes the order total and reproducible.

SELECT
    sender_id,
    COUNT(*) AS msg_count
FROM chat_msgs
WHERE content <> ''
  AND sent_at >= '2026-01-01'
GROUP BY sender_id
HAVING COUNT(*) > 2
;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you narrow this to just the last 24 hours? (Tighten the WHERE to `sent_at` >= NOW() - INTERVAL '1 day' so the partition key prunes the scan even harder.)
• What if we only care about senders who flood a single channel? (Add channel to the grouping: GROUP BY `sender_id`, channel HAVING COUNT(*) > 2. The grain changes to sender-channel, so a sender flagged overall may vanish per channel.)
• How would you express the threshold without HAVING? (Wrap the aggregation in a CTE and filter on `msg_count` > 2 in an outer WHERE. Same plan in most engines, but it shows HAVING is just a post-group WHERE.)