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…