# Where the Talking Stops > Which channels are ghost towns? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The community team is auditing channel health, from the busiest rooms down to the ghost towns. For each channel with at least five messages, report the total message count, how many different people posted, and what percentage of messages were later edited, busiest first. ## Worked solution and explanation ### What this really is Beneath the channel-health framing this is three separate aggregations sharing one GROUP BY, and the edited percentage is the one that separates people. Anyone writes COUNT(*) and COUNT(DISTINCT sender_id) side by side without thinking. The trap is the ratio: SUM(CASE WHEN edited = 1 THEN 1 ELSE 0 END) is an integer and COUNT(*) is an integer, so dividing them does integer division and every channel floors to 0. Multiply by 100.0 (not 100) first and #general comes back as 50.0; forget the decimal and it silently reports 0.0. --- ### Break down the requirements #### Step 1: Group by channel `GROUP BY channel` produces one row per channel. #### Step 2: Compute three metrics `COUNT(*)` for total messages, `COUNT(DISTINCT sender_id)` for unique senders, and `ROUND(100.0 * SUM(CASE WHEN edited = 1 THEN 1 ELSE 0 END) / COUNT(*), 1)` for edited percentage. The `100.0` forces floating-point division so the ratio does not floor to zero. #### Step 3: Filter and sort `HAVING COUNT(*) >= 5` drops low-traffic channels after grouping, since the filter is on an aggregate. `ORDER BY total_messages DESC` puts the busiest channels first. --- ### The solution **Multi-metric channel health report** ```sql 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(*) >= 5 ORDER BY total_messages DESC ``` > **Cost Analysis** > > Single scan of 10M rows. The COUNT(DISTINCT sender_id) requires maintaining a hash set per channel, but channel cardinality is typically low (hundreds), keeping memory usage reasonable. > **Interviewers Watch For** > > The tell here is the percentage math. A candidate who writes SUM(...) / COUNT(*) with plain integers and does not notice every result is 0.0 has not internalized how integer division behaves, and a senior one either casts or leads with the 100.0 without being prompted. > **Common Pitfall** > > Using `AVG(edited)` works if the column is strictly 0/1, but silently misreports if it ever holds other values, and it returns a fraction rather than a percentage. Explicit conditional aggregation with CASE is safer and states the intent. --- ## Common follow-up questions - How would you identify channels where the edited percentage is increasing over time? _(Tests time-series analysis with LAG or monthly aggregation.)_ - What if edited is a boolean column, not integer? _(Tests casting: SUM(CAST(edited AS INT)) or COUNT(*) FILTER (WHERE edited).)_ - How would you also show the most recent message timestamp per channel? _(Adding MAX(sent_at) to the SELECT list.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_talking_stops) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.