Where the Talking Stops

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

The community team is auditing which channels host real back-and-forth, not one-way announcements. For each channel where at least one message is a reply to another, report the total message count, how many different people posted, and what percentage of messages were later edited, busiest first and, where two channels are equally busy, listed alphabetically by name.

Which channels are ghost towns?

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 really is

Beneath the channel-health framing this is a grouped report with two filters that live at different levels, and telling them apart is the whole game. The reply requirement tempts you toward WHERE reply_to IS NOT NULL, which prunes rows before the GROUP BY and quietly shrinks every count down to just the replies. It belongs in HAVING, applied after the grouping, so a channel qualifies on its reply activity while total_messages still counts every message. The second trap is the edit share: SUM(CASE WHEN edited = 1 THEN 1 ELSE 0 END) and COUNT(*) are both integers, so dividing them floors to 0 for every channel. Lead with 100.0 and #general comes back as the real 50.0; forget it and the column silently reports 0.0 everywhere.

Break down the requirements

Step 1: Group by channel

GROUP BY channel collapses the message log into one row per room, the grain every metric is measured at.

Step 2: Compute the three metrics

COUNT(*) gives total messages and COUNT(DISTINCT sender_id) the number of different posters. For the edit share, ROUND(100.0 * SUM(CASE WHEN edited = 1 THEN 1 ELSE 0 END) / COUNT(*), 1): the 100.0 forces floating-point division so the ratio does not floor to zero.

Step 3: Gate channels, then sort

HAVING COUNT(reply_to) > 0 keeps only channels that contain at least one reply, and because COUNT(col) ignores nulls it counts exactly the reply rows. Putting the gate in HAVING rather than WHERE lets it judge the whole channel without shrinking its counts. ORDER BY total_messages DESC, channel puts the busiest first and breaks ties alphabetically.

The solution

SELECT
    channel,
    COUNT(*) AS total_messages,
    COUNT(DISTINCT sender_id) AS unique_senders,
    ROUND(100.0 * SUM(CASE WHEN edited = 1 THEN 1 ELSE 0 END) / COUNT(*), 1) AS edited_pct
FROM chat_msgs
GROUP BY channel
HAVING COUNT(reply_to) > 0
ORDER BY total_messages DESC, channel

Common follow-up questions

• How would you surface the ghost towns instead, the channels with no replies at all? (Tests flipping the aggregate predicate, or a left join against the full channel list to keep the empties.)
• What if `edited` were a boolean column instead of a 1/0 integer? (Tests casting or a filtered aggregate: SUM(CAST(edited AS INT)) or COUNT(*) FILTER (WHERE edited).)
• How would you order the channels by edit rate rather than by volume? (Tests moving the computed edit-share expression into ORDER BY.)