The Loudest Rooms

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

L4

Problem

We run a community app where the people who post in chat channels are the same people we serve ads to, and a channel is credited with a poster's ad impressions once for every message they sent in it. Find each channel's impression count from ad_impressions and chat_msgs, busiest first.

Every channel has an audience. Find where it is biggest.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

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

Strip the community-app costume and this is a many-to-many attribution. Impressions live in ad_impressions with no channel. Channels live in chat_msgs with no impressions. The only bridge is one person id that goes by two names, user_id and sender_id. Anyone can write that join. The trap is assuming channels split the audience into separate buckets. On this data every poster talks in exactly two channels, with two messages in each. So a single impression legitimately lands in two channels and is credited twice in each. Treat the channels as a partition, or dedupe impressions before attributing them, and your six counts sum to 180 instead of 720, with every channel undercounted.

Step 1: Anchor on ad_impressions

The thing being counted is an impression, so start there. The channel is not on this table, so you have to reach it through the person who saw the ad.

Step 2: Bridge on the person id

Inner join chat_msgs on ai.user_id = cm.sender_id. The 20 impressions with a NULL user_id match nothing and drop out. That is right: an impression with no viewer reached no channel's posters.

Step 3: Count at the join grain

Group by cm.channel and take COUNT(*). Every sender here has two messages in each of their two channels, so each of their impressions shows up twice per channel. That is the metric itself: a channel is credited once for every message the viewer posted there.

Step 4: Sort busiest first

Order by impression_count descending. The six counts are all different on this data, from 152 down to 98, so there is nothing to break. If two did tie, either order would still be busiest first.

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;

`COUNT(*)`

Counts joined rows: one per impression per message the viewer sent in that channel. '#deployments' lands at 152, the message-credited count the statement defines.

`COUNT(DISTINCT ai.impression_id)`

Counts each impression once per channel and returns 76 for '#deployments'. It answers 'how many ads reached this audience', which is a different question from the one asked here.

Common follow-up questions

• Product now wants each impression counted once per channel, however often the viewer posts there. What changes? (Moves from `COUNT(*)` to counting unique `impression_id` values per channel.)
• Rewrite it to count only clicked impressions per channel. (Adds a `clicked = 1` filter before the aggregate.)
• How would you also return channels whose posters saw no ads at all? (Forces a `chat_msgs`-anchored left join and a count of `ai.impression_id` instead of `COUNT(*)`.)