Top Active Senders per Channel

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

Asked in

Meta

Problem

When several people tie for the most messages in a channel, every one of them counts as that channel's busiest sender, not just one. From chat_msgs, an analyst needs the busiest senders of each channel since July 1, with channels in alphabetical order.

Top three messages per channel by replies.

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

This is a per-group argmax with ties, dressed up as a 'busiest member' badge. Two things decide the answer, and the seed punishes getting either one wrong. First, 'busiest' is scoped to the channel. Since July 1, four channels have one sender at 2 messages, while '#general' and '#random' top out at 1, with seven and six senders tied. Compare everyone to the overall top count and those two channels vanish. Second, ties stay: keep one row per channel and 11 senders who are legitimately the busiest disappear. Drop the date filter and every channel-sender pair ties at 2, and the answer balloons to all 100 pairs.

Step 1: Count each sender's messages per channel inside the window

Filter chat_msgs to sent_at >= '2026-07-01' before GROUP BY channel, sender_id with COUNT(*). The filter changes the counts, so it has to run first. sent_at is ISO text, so the string comparison orders dates correctly. Do not filter on msg_type, edited or reply_to: every message counts.

Step 2: Place senders within each channel

DENSE_RANK() OVER (PARTITION BY channel ORDER BY msg_count DESC) gives every sender tied at the channel's top count place 1. The PARTITION BY is what makes 'top' mean this channel's top and not the workspace's.

Step 3: Keep place 1 in an outer step

A window result cannot be filtered in the WHERE of the SELECT that computes it, so the ranked CTE exists to let WHERE place = 1 run afterwards.

Step 4: Sort by channel

The list reads channel by channel, so ORDER BY channel is the whole order. Senders tied inside a channel may come in any order, and none is asked for.

The solution

WITH recent_counts AS (
    SELECT channel, sender_id, COUNT(*) AS msg_count
    FROM chat_msgs
    WHERE sent_at >= '2026-07-01'
    GROUP BY channel, sender_id
),
ranked AS (
    SELECT channel, sender_id, msg_count,
        DENSE_RANK() OVER (PARTITION BY channel ORDER BY msg_count DESC) AS place
    FROM recent_counts
)
SELECT channel, sender_id, msg_count
FROM ranked
WHERE place = 1
ORDER BY channel

Global top count

WHERE msg_count = (SELECT MAX(msg_count) FROM recent_counts) keeps the four senders at 2 messages. '#general' and '#random', whose busiest senders posted once, drop out entirely.

Per-channel top count

PARTITION BY channel resets the comparison per channel, so the seven tied senders in '#general' and the six in '#random' all come back: 17 rows.

WITH recent_counts AS (
    SELECT channel, sender_id, COUNT(*) AS msg_count
    FROM chat_msgs
    WHERE sent_at >= '2026-07-01'
    GROUP BY channel, sender_id
),
numbered AS (
    SELECT channel, sender_id, msg_count,
        ROW_NUMBER() OVER (PARTITION BY channel ORDER BY msg_count DESC) AS rn
    FROM recent_counts
)
SELECT channel, sender_id, msg_count
FROM numbered
WHERE rn = 1
ORDER BY channel

Common follow-up questions

• What changes if the list should show the top three distinct counts per channel? (Tests that `DENSE_RANK()` with `place <= 3` keeps tiers, while `RANK()` would skip places after a tie.)
• How would you leave out messages whose `msg_type` is 'system', given it is stored in several cases? (Tests normalizing with `LOWER(msg_type)` in the `WHERE` before the count.)
• If the window became the last 30 days, what moves? (Tests swapping the fixed start for a date relative to today, still applied before `COUNT(*)`.)