Interview Question Bank · LAG with an offset · ids with gaps
5. The same code three times in a row
MediumGeneralQuestion #197
Concept: LAG with an offset · ids with gapsshow
To compare a row with the rows just before it, use LAG(col) OVER (ORDER BY id) and LAG(col, 2) OVER (ORDER BY id). They follow the sort order, so they still work when ids have gaps, unlike a self-join on id + 1. A value that repeats four or more times makes several matching windows, so finish with DISTINCT.
Example (different data):
SELECT reading_id, sensor_state, LAG(sensor_state) OVER (ORDER BY reading_id) AS one_before, LAG(sensor_state, 2) OVER (ORDER BY reading_id) AS two_before FROM sensor_readings;
Your task
A packing machine at Tidewell Foods logs a fault code every time it stops. Maintenance treats a code that shows up three times in a row as a real fault, not a fluke. Some log rows were deleted, so the ids have gaps.
Return fault_code for every code that appears at least three times in a row when the log is read in id order. List each code once, sorted by fault_code.
Starting Postgres in your browser…
Checking your access…