Using a self-join, how do you pair each sensor reading with the previous reading for that device?

## "Previous" is not a join concept A join pairs rows that satisfy a predicate. It has no cursor, no ordering, and no idea what "the row before" means. So the whole task is to write a predicate that *characterises* the previous row, and there are only two honest ways to do that: say it is the maximum of the earlier rows, or say no row lies strictly between it and the current one. ## Formulation 1: the nearest earlier row is the maximum ```sql SELECT c.reading_id, c.taken_at, p.taken_at AS prev_at, c.value - p.value AS delta FROM readings AS c JOIN readings AS p ON p.device_id = c.device_id AND p.taken_at = (SELECT MAX(r.taken_at) FROM readings AS r WHERE r.device_id = c.device_id AND r.taken_at < c.taken_at); ``` The correlated subquery computes, for each current row, the largest timestamp strictly below it within the same device; the join then fetches that row. ## Formulation 2: nothing in between ```sql FROM readings AS c JOIN readings AS p ON p.device_id = c.device_id AND p.taken_at < c.taken_at WHERE NOT EXISTS (SELECT 1 FROM readings AS m WHERE m.device_id = c.device_id AND m.taken_at > p.taken_at AND m.taken_at < c.taken_at) ``` This reads as the definition of adjacency: `p` precedes `c` and nothing sits between them. Many people find it the clearest statement of intent. Both formulations produce the same rows. ## Why the reduction step is mandatory Drop the `NOT EXISTS` or the `MAX` and you no longer have consecutive pairs — you have every earlier row paired with every later one. For a device with 5 readings the 5th row alone produces 4 output rows and the device produces 10 in total. The symptom in the wild is a "change since last reading" report whose row count is far larger than the table and whose deltas look wildly wrong. If you see triangular growth in a result set, an adjacency reduction is missing. ## The offsets-by-arithmetic trap The tempting shortcut is `ON p.reading_id = c.reading_id - 1`. It is almost always wrong: - Surrogate keys have **gaps** — rolled-back transactions, deletes, cached sequence blocks — so `id - 1` may not exist and the row silently drops out. - With several devices writing into one table the ids **interleave**, so `id - 1` is usually a different device's reading. - Ids are not guaranteed to be assigned in timestamp order under concurrency. The arithmetic is only defensible over a dense, gap-free sequence you generated yourself, for example a row number materialised in a prior step. ## Partitioning and ties Every predicate that defines adjacency must repeat the partitioning column. `p.device_id = c.device_id` appears in the join *and* inside the `NOT EXISTS`; forget it in the inner query and a reading from another device will "come between" two of yours and wipe out legitimate pairs. Ties are the other correctness hazard. If two readings for one device share a timestamp, `<` excludes both of them from being each other's predecessor and the pair count changes shape — the second formulation may return two predecessors for one row. Adjacency needs a **total order**, so add a deterministic tiebreaker to every comparison, comparing row values: `(p.taken_at, p.reading_id) < (c.taken_at, c.reading_id)`. ## Keeping the first row The earliest reading of each device has no predecessor and disappears from an inner join. If the report needs a row per reading, use `LEFT JOIN` and let the predecessor columns come back NULL — then guard the arithmetic, since `c.value - p.value` is NULL when `p` is missing. ## Scope and the modern alternative The adjacency self-join is the portable formulation and predates window functions, which entered the standard in SQL:2003. Where your engine supports them, `LAG(value) OVER (PARTITION BY device_id ORDER BY taken_at)` expresses the same thing in one pass and without the correlated reduction; it is worth naming in an interview so it is clear you know the self-join is a deliberate choice, not the only tool. The self-join formulation still earns its keep when you need the *whole* predecessor row, when adjacency is defined by something more complex than an ordering (nearest earlier reading **of the same type**, say), or on an engine or a view definition where windows are unavailable. ## What the interviewer is testing That you can define adjacency as a predicate rather than reaching for a positional operator that does not exist; that you spot the id-arithmetic trap; that you repeat the partition key everywhere; and that you know the first row vanishes unless you ask for it.