Unique Stream Topics

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
easy
Seniority
L3

Problem

Topic names are case-sensitive, so two names that differ only in letter case are separate topics. An on-call engineer rebuilding the topic registry needs each topic that appears in stream_msgs listed once.

A clean inventory of streaming topics.

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 40 rows
topic
order-updates
page-views
click-stream
payment-events
inventory-sync

Worked solution and explanation

This is set extraction dressed up as a registry build. The skill is noticing that the answer's grain is coarser than the table's: stream_msgs holds one row per message, and the registry wants one row per topic. Anyone can type SELECT topic FROM stream_msgs. The candidates who pass notice that it returns 200 rows on this seed, one per message, instead of the 40 topics. On a real cluster that same query hands the registry hundreds of millions of rows, buried under the hottest topics.

Getting to the set

Step 1: Name the grain of the answer

A registry row is a topic, not a message. In stream_msgs the topic column repeats once for every message written to it, so 'order-updates' appears on many rows. Say that mismatch out loud before writing anything: it is the whole problem.

Step 2: Collapse the repeats

SELECT DISTINCT topic keeps one row per value. GROUP BY topic with no aggregate computes the same set and is an equally good answer. Reach for it when you expect the next question to be how many messages each topic carries.

Step 3: Leave the values as stored

Topic names are case-sensitive, so 'User-Events' and 'user-events' are two topics, and both belong in the registry. The names that look like people ('Una Bennett') are topics too. There is no filter and no cleanup: the registry reflects what the cluster holds.

The solution

Distinct values from a high-volume table
SELECT DISTINCT topic
FROM stream_msgs
One row per message
SELECT topic FROM stream_msgs returns 200 rows here, with each topic repeated once for every message on it.
One row per topic
SELECT DISTINCT topic FROM stream_msgs returns 40 rows, each topic name exactly once.

Common follow-up questions