# The Loudest Rooms > Every channel has an audience. Find where it is biggest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We run a community app where the people who post in chat channels are the same people we serve ads to. For each channel, find how many ad impressions reached the people who post in it, busiest first. ## Worked solution and explanation ### What this really is Strip the community-app costume and this is a keyed intersection. The people who see ads and the people who post in channels are the same population, linked only by a shared person id that goes by two names: `user_id` on the impressions side, `sender_id` on the chat side. The skill being probed is whether you can express 'impressions belonging to people who post here' as an inner join on that id and then tally per channel, without letting the join grain lie to you. Anyone can write the join. What separates candidates is knowing exactly which impressions the join silently drops and what COUNT is actually counting once rows line up. > **The join is doing two jobs** > > That one inner join both filters and labels. It filters, because only impressions whose user posted somewhere survive, and it labels, because it staples a channel onto each surviving impression. Once you see it as filter-plus-label, the grouping by channel and the count fall straight out. **Impressions per channel, busiest first** ```sql SELECT cm.channel AS channel, COUNT(*) AS impression_count FROM ad_impressions AS ai INNER JOIN chat_msgs AS cm ON ai.user_id = cm.sender_id GROUP BY cm.channel ORDER BY impression_count DESC, cm.channel ASC; ``` *Inner join on the shared person id, one count per channel, deterministic ordering.* #### Step 1: Anchor on the impressions Start from `ad_impressions`, because the thing you are counting is an impression. Every row here is one ad served. The channel is not on this table yet, so you have to reach for it. #### Step 2: Reach the channel through the person Inner join to `chat_msgs` on `ai.user_id` = `cm.sender_id`. This is the only bridge between the two tables. The inner keyword is the whole point: an impression served to someone who never posted has no matching sender row and correctly falls away. #### Step 3: Collapse to one row per channel Group by cm.channel and count the surviving rows. Each surviving row is an (impression, channel) pair, so on data where a poster sits in a single channel the count is exactly the impressions that reached that channel's members. #### Step 4: Order so the answer is stable Sort by `impression_count` descending for busiest first, then by channel name ascending. That second key is not decoration: three channels tie at two impressions, and without a tiebreak the engine is free to return them in any order. > **The inner join is deleting rows on purpose** > > One impression has `user_id` 100 and another has a null `user_id`. Neither person posted, so both vanish under the inner join, which is what you want. Switch to a left join to be 'safe' and you resurrect them with a null channel, adding a phantom group and inflating nothing useful. The filtering is the feature. **COUNT(*)** Counts joined rows. Correct here because every poster has exactly one message, so an impression matches exactly one chat row. **COUNT(DISTINCT `ai.impression_id`)** Counts distinct impressions. This is what you switch to the moment a poster can have several messages in the same channel, otherwise each extra message double counts that person's impressions. > **They are watching the ORDER BY** > > A candidate who writes ORDER BY `impression_count` DESC and stops has technically ordered the result, but the three-way tie at two impressions will flicker between runs. Adding the channel name as a tiebreak is the tell that you think about determinism, not just sorting. > **One pass each, cheap at scale** > > This is a single equi-join on the person id followed by one aggregation. Both tables are scanned once; with an index on `chat_msgs`.sender_id the join stays a hash or index probe rather than a nested scan. Nothing here degrades super-linearly as impressions grow into the billions. ## Common follow-up questions - A poster is active in several channels. How does the impression count change, and is that the behavior the business wants? _(Tests whether the candidate sees that a multi-channel poster fans their impressions into every channel they post in.)_ - Rewrite it to count only clicked impressions per channel. _(Adds a WHERE clicked = 1 filter and checks they filter before aggregating.)_ - How would you also return channels that received zero qualifying impressions? _(Forces the switch to a chat-anchored left join and a COUNT of the impression key rather than COUNT(*).)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_loudest_rooms) - [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.