Low-Volume Stream Topics

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
L3
Asked in
Amazon

Problem

We run a streaming platform and are building a compaction worklist of the quietest topics. List each topic that carries fewer than 6 messages, along with its message count.

Quiet topics in the stream.

Tables

stream_msgs

Columns: msg_id INTEGER, topic TEXT, part_key TEXT, payload TEXT, offset INTEGER, produced TEXT, consumer TEXT

stream_msgs: 10 of 200 rows
msg_idtopicpart_keypayloadoffsetproducedconsumer
10079order-updateskey-3{"event": "type_1", "ts": 1700003600, "data": {"id": 1}}1002026-02-02 01:07:13search-indexer
10158page-viewskey-6{"event": "type_2", "ts": 1700007200, "data": {"id": 2}}2002026-03-03 02:14:26ml-pipeline
10237click-streamkey-9{"event": "type_3", "ts": 1700010800, "data": {"id": 3}}3002026-04-04 03:21:39reporting-svc
10316payment-eventskey-12{"event": "type_4", "ts": 1700014400, "data": {"id": 4}}4002026-05-05 04:28:52NULL
10395inventory-synckey-15{"event": "type_5", "ts": 1700018000, "data": {"id": 5}}5002026-06-06 05:35:05notification-svc
10474audit-logkey-18{"event": "type_6", "ts": 1700021600, "data": {"id": 6}}6002026-07-07 06:42:18NULL
10553metric-ingestNULL{"event": "type_7", "ts": 1700025200, "data": {"id": 7}}7002026-08-08 07:49:31data-warehouse
10632Una Bennettkey-24{"event": "type_8", "ts": 1700028800, "data": {"id": 8}}8002026-09-09 08:56:44NULL
10711Van Woodkey-27{"event": "type_9", "ts": 1700032400, "data": {"id": 9}}9002026-01-10 09:03:57fraud-detector
10790Wes Barneskey-30{"event": "type_0", "ts": 1700036000, "data": {"id": 10}}10002026-02-11 10:10:10analytics-svc

Expected output

First 5 of 20 rows
topicmsg_count
Han Washington4
Isa Butler4
Joy Simmons4
Kai Foster4
Lea Gonzales4

Worked solution and explanation

What this really asks

Beneath the compaction story this is a filter on a GROUP, not on a row. You are asked for topics whose *total* message count sits under a threshold, and that total does not exist until every row for a topic has been folded together. Anyone can write GROUP BY topic; what separates people is knowing the threshold has to ride on HAVING COUNT(*) < 6, because WHERE runs before the grouping and never sees the count. Reach for WHERE COUNT(*) < 6 and the query will not even compile.

Build it

Step 1: Collapse each topic into one group

GROUP BY topic folds every message that shares a topic into a single group, including rows with a null consumer or part_key. Those are real messages and belong in the count.

Step 2: Count the messages per topic

COUNT(*) counts every row in each group. Use COUNT(*), not COUNT(consumer), so null-consumer rows are not silently dropped from the tally.

Step 3: Keep only the quiet groups

HAVING COUNT(*) < 6 runs after the grouping and keeps only the topics whose total is under the threshold. A topic sitting at exactly 6 is busy enough to keep, so it is excluded.

HAVING for a post-aggregation threshold
SELECT topic, COUNT(*) AS msg_count
FROM stream_msgs
GROUP BY topic
HAVING COUNT(*) < 6
WHERE (wrong)
WHERE COUNT(*) < 6 fails: at row-filter time there is no group yet and no count to compare against. No row-level predicate can express a per-topic total.
HAVING (right)
HAVING COUNT(*) < 6 runs after GROUP BY topic collapses each topic to one row, so the count exists and the threshold applies to the whole group.

Common follow-up questions