How can I compare each row with the previous row using LAG?

Use LAG() to fetch the preceding reading, then subtract it from the current one. Put the date in the window’s ORDER BY so SQLite knows the sequence, and use PARTITION BY device so a reading is compared only with another reading for the same device. [S1]

For example, suppose a table called readings has these columns: device, month, and reading. This query returns each reading, the previous reading for that device, and the difference:

SELECT
  device,
  month,
  reading,
  LAG(reading) OVER (
    PARTITION BY device
    ORDER BY month
  ) AS previous_reading,
  reading - LAG(reading) OVER (
    PARTITION BY device
    ORDER BY month
  ) AS change_from_previous
FROM readings
ORDER BY device, month;

Here, “change” means current reading minus previous reading. A positive result means the current value is larger; a negative result means it is smaller. The window’s order determines which row counts as preceding, so use a month value that sorts in the intended chronological order. [S1]

Here is an invented example. Assume the input rows are:

Device | Month   | Reading
A      | 2025-01 | 10
A      | 2025-02 | 13
A      | 2025-03 | 11
B      | 2025-01 | 7
B      | 2025-02 | 9

The query produces:

Device | Month   | Reading | Previous | Change
A      | 2025-01 | 10      | NULL     | NULL
A      | 2025-02 | 13      | 10       | 3
A      | 2025-03 | 11      | 13       | -2
B      | 2025-01 | 7       | NULL     | NULL
B      | 2025-02 | 9       | 7        | 2

The first row for each device has no preceding row within that device’s partition, so LAG() returns NULL. Subtracting that NULL gives a NULL change as well. [S1] In this example, A’s February change is 13 - 10 = 3, and its March change is 11 - 13 = -2.

Check the two parts of the window definition separately:

1. PARTITION BY device keeps each device’s comparison series separate. If you leave it out, rows can be compared across devices rather than within each device. [S1]
2. ORDER BY month defines the sequence used to find the preceding row. [S1] Make sure the month column sorts in the order you intend. For example, text months such as 2025-01 and 2025-02 are a clear, consistent format in this sample.

If multiple rows for one device can have the same month, decide how you want those rows ordered and add a suitable tie-breaker column to the window’s ORDER BY. The example assumes one reading per device per month; it does not establish how duplicate-month readings should be interpreted.

Yes. Replace reading with the numeric column you want to compare, while keeping the partition and ordering appropriate to that measure. For instance, the same pattern can compare each device’s monthly usage if your table has a numeric usage column.

The worked query assumes the table and columns shown are named readings, device, month, and reading, and that you want the current value minus the previous value. Change those names to match your schema. The cited SQLite documentation establishes the behavior of LAG(), its partitioning and ordering, and the first-row NULL; it does not establish your data’s units, whether readings are complete, or how repeated months should be handled. [S1]