SQL LAG: Compare With the Previous Row, Not an Assumed Previous Day

The practice task

Compare fictional temperature readings with the preceding recorded reading. The dates intentionally skip October 2 so the meaning of previous row is visible.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE temperatures(day TEXT, value INTEGER);
INSERT INTO temperatures VALUES ('2026-10-01',20),('2026-10-03',24),('2026-10-04',23);

SELECT day, value, LAG(value) OVER (ORDER BY day) AS prior_reading,
 value - LAG(value) OVER (ORDER BY day) AS change
FROM temperatures ORDER BY day;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The first reading has no earlier recorded row, so the prior value and change are NULL. October 3 compares with October 1 and increases by four. October 4 compares with October 3 and decreases by one.

Calling the October 3 result a one-day change would be wrong because the source has a gap. You could show the previous date as well, fill a deliberate calendar grid or use a date-specific join depending on the actual reporting question.

The window order defines which reading is prior. If several readings share a date, add a timestamp or stable sequence to establish their order. A non-unique ordering key can leave a previous-row comparison without the deterministic meaning the report requires.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Distinguish previous observed row from previous calendar period.
• Define a stable order when timestamps can tie.
• Keep the first-row missing comparison explicit.