Alert Severity

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

L5

Problem

Offsets are showing up twice in topic after topic of stream_msgs, and the on-call engineer wants each message lined up against the one just before it in its topic, in offset order. For every message that has an earlier one in its topic, show its offset, the prior offset, how many different offsets the topic has reached so far, the message's position in the topic, and the fraction of messages up to that point that carried a new offset.

When the alarms go off, who screams loudest?

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

What this really is

This is a duplicate-delivery audit dressed up as offset bookkeeping. Every offset in stream_msgs lands twice in its topic, so the real question is whether you can keep two different counts apart over one ordering: the unique offsets a topic has reached (DENSE_RANK()) and the messages it has delivered (ROW_NUMBER()). Anyone can write the three window calls. What separates candidates is the last column: msg_rank / msg_row_num is integer over integer, and on this seed it truncates to 0 on every one of the 160 rows. The quieter failure is removing each topic's first message too early, which renumbers everything after it.

Walk it

Step 1: Order each topic by offset

offset is an integer, so PARTITION BY topic ORDER BY offset already sorts numerically. All three window functions share that one spec, so the engine sorts each topic once and derives three columns from the same pass.

Step 2: Keep the unique count and the position apart

The two '5400' rows in 'Ada Jenkins' tie on the order key. DENSE_RANK() gives both a 2 (two unique offsets reached), while ROW_NUMBER() gives them 3 and 4 (third and fourth message delivered). The gap between the two counts is exactly the replays the on-call engineer is chasing.

Step 3: Filter after the windows run

prev_offset is NULL only on each topic's first message, and a WHERE cannot test a window result computed in the same SELECT. Compute the windows in a derived table and drop prev_offset IS NULL outside it, so the surviving rows keep the positions they earned. That is why the preview starts at msg_row_num 2.

Step 4: Make the share fractional

msg_rank and msg_row_num are both integers, and integer division floors: 2 / 3 is 0. CAST(msg_rank AS REAL) turns the numerator into a float, so the same division returns 0.667.

The solution

SELECT
  topic,
  offset,
  prev_offset,
  msg_rank,
  msg_row_num,
  CAST(msg_rank AS REAL) / msg_row_num AS new_offset_share
FROM (
  SELECT
    topic,
    offset,
    LAG(offset) OVER (PARTITION BY topic ORDER BY offset) AS prev_offset,
    DENSE_RANK() OVER (PARTITION BY topic ORDER BY offset) AS msg_rank,
    ROW_NUMBER() OVER (PARTITION BY topic ORDER BY offset) AS msg_row_num
  FROM stream_msgs
) AS positioned
WHERE prev_offset IS NOT NULL

Integer over integer

msg_rank / msg_row_num returns 0 on all 160 rows. Past each topic's first message, the unique offsets reached are always fewer than the messages delivered, so every quotient sits below 1 and floors away.

Cast first

CAST(msg_rank AS REAL) / msg_row_num returns 0.5, 0.667, 0.5, 0.6 down 'Ada Jenkins': the share of messages so far that actually brought a new offset.

Common follow-up questions

• How would you flag rows where `offset - prev_offset` is larger than the topic's normal step, so missing messages surface alongside the replays? (Extends the `LAG` column into a gap check.)
• How would you keep only one copy per `offset` in each topic, preferring the earliest `produced`? (Tests per-key deduplication with `ROW_NUMBER()` over a different ordering.)
• `topic` is zipf-skewed. How does that affect window parallelism, and what would you do about it? (Tests skew awareness: sub-ranging or salting hot partitions.)