Duplicated User Event Messages

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

What feed does an engineer load-testing the consumers replay when she pushes twice the normal volume of the 'user-events' topic in stream_msgs, with every message sent as two identical copies? She replays that feed in message ID order.

Duplicated messages from the alerts topic.

Tables

stream_msgs

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

Expected output

Worked solution and explanation

It is a fan-out, not a dedup

Strip the Kafka costume and this is a controlled row fan-out: take the 'user-events' slice of stream_msgs and emit each row exactly twice. The entire problem hinges on one keyword choice. UNION ALL keeps every duplicate; plain UNION silently throws them away. On the seed that is the difference between 8 rows and 4. Candidates who reach for UNION because it 'reads cleaner' ship a load test at half the intended throughput while every dashboard reports green.

Build it in three moves

Step 1: Carve out the user-events slice

Filter stream_msgs to topic = 'user-events' and project only the six columns the replay tool needs: msg_id, topic, part_key, payload, produced, consumer. offset is intentionally left out: a replayed message lands at a new offset, so it is not part of the identity you are copying.

Step 2: Stack the slice onto itself

Write that filtered slice once, then stack a byte-identical copy beneath it with UNION ALL between the two SELECT statements. ALL is the load-bearing word: it preserves duplicates, so every qualifying row now appears exactly twice. Swap in plain UNION and the two copies fold back into one.

Step 3: Sort the combined feed by msg_id

Name the doubled slice as the CTE doubled, then put a single ORDER BY msg_id on the outer select. The CTE makes it obvious to a reader that the sort applies to the combined result rather than to one branch, and it puts the two copies of each message next to each other in the order the replayer sends them.

The solution

WITH doubled AS (
  SELECT msg_id, topic, part_key, payload, produced, consumer
  FROM stream_msgs
  WHERE topic = 'user-events'
  UNION ALL
  SELECT msg_id, topic, part_key, payload, produced, consumer
  FROM stream_msgs
  WHERE topic = 'user-events'
)
SELECT msg_id, topic, part_key, payload, produced, consumer
FROM doubled
ORDER BY msg_id

UNION ALL (correct)

Keeps every row from both branches. The four matching messages become eight output rows: exactly the doubled load you asked for.

UNION (wrong)

Applies an implicit distinct across the combined set. The two identical copies collapse into one, so four messages stay four rows and the replay adds no load.

Common follow-up questions

• If the replay factor became configurable to N copies instead of 2, how would you produce N without writing N stacked SELECTs? (Tests generalizing a fixed fan-out into a parameterized join.)
• Going the other direction: how would you collapse genuine replay duplicates and keep only the earliest offset per message? (Tests `ROW_NUMBER` partitioning to pick a single occurrence.)
• How would you stop replayed duplicates from being double-counted at the consumer level in the first place? (Tests idempotent consumer design beyond SQL.)