Drill 6 of 12 in Data engineering questions
0

Interview Question Bank · LEAD · PARTITION BY · gap ranges

6. Missing offsets in a stream

MediumData engineeringQuestion #246

Concept: LEAD · PARTITION BY · gap rangesshow

To find holes in a sequence, put each value next to the one after it with LEAD(value) OVER (ORDER BY value). Wherever next minus current is more than 1, the values in between are missing: the gap runs from current + 1 to next - 1. Partition by the sequence's owner so one sequence's last value isn't compared with another's first.

Example (different data):

SELECT till_id, receipt_no + 1 AS first_missing, next_no - 1 AS last_missing
FROM (
  SELECT till_id, receipt_no,
    LEAD(receipt_no) OVER (PARTITION BY till_id ORDER BY receipt_no) AS next_no
  FROM receipts
) r
WHERE next_no - receipt_no > 1;

Your task

Fernhill's clickstream consumer records every message offset it ingests in de_stream_offsets. Offsets within a partition should be consecutive, so any hole means lost messages that must be replayed.

For each partition, return every gap: partition_id, gap_start (first missing offset), gap_end (last missing offset) and missing_count. A replayed message repeats an offset, which is not a gap. Sort by partition_id, then gap_start.

Starting Postgres in your browser…

Checking your access…