Why does a flagged sample lose its actual previous reading?

Prepare the report input

Original report shows flagged samples beside their immediate previous recorded reading by id. Filtering flagged=1 inside the window input removes unflagged history. Compute LAG in a full-history CTE and then select flagged rows.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE samples(id INTEGER PRIMARY KEY,reading INTEGER NOT NULL,flagged INTEGER NOT NULL CHECK(flagged IN(0,1))); INSERT INTO samples VALUES(1,5,0),(2,8,1),(3,9,1);

Compare misleading and corrected results

SELECT id,reading,LAG(reading) OVER(ORDER BY id) AS previous FROM samples WHERE flagged=1 ORDER BY id;

WITH history AS (SELECT *,LAG(reading) OVER(ORDER BY id) AS previous FROM samples) SELECT id,reading,previous FROM history WHERE flagged=1 ORDER BY id;

Sample 2 should show previous 5, but the bad query shows NULL because sample 1 was removed. Sample 3 correctly follows sample 2 in both results.

Check a fresh boundary case

Flag the first sample too. It legitimately has no prior row and remains NULL, while later prior values stay five/eight.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

UPDATE samples SET flagged=1 WHERE id=1;
WITH history AS (SELECT *,LAG(reading) OVER(ORDER BY id) AS previous FROM samples) SELECT id,reading,previous FROM history WHERE flagged=1 ORDER BY id;