Finding the difference in rows in query using SQLite

Question

I have an SQLite table that contains prices for various products. It's a snapshot table, so it contains the prices on 5 minute intervals. I would like to write a query that would return the difference in price from one row to the next on each item. The columns are id (auto inc), record_id (id of the product), price (price at that point in time), time (just seconds since epoch) I'm trying to return a 'difference' column that contains a value of difference between intervals. ``` Given the following id record_id price time 1 apple001 36.00 ... 67 apple001 37.87 ... 765 apple001 45.82 ... 892 apple001 26.76 ... I'd like it to return id record_id price time difference 1 apple001 36.00 ... 0 67 apple001 37.87 ... 1.87 765 apple001 45.82 ... 7.95 892 apple001 26.76 ... -19.06 ``` Is it possible with SQLite? Secondly, should it be possible - is there a way to limit it to the last 5 or so records? I'd appreciate any help, thanks. Just wanted to add a few things. I've found ways to do so in oth…

Accepted Answer

Yes, it is a bit late, but for completeness. SQLite Release 3.25.0 in 2018 added support for window functions. And the above task can now be completed by using the LAG() and LEAD() functions.

Taken from: https://www.sqlitetutorial.net/sqlite-window-functions/

LAG Provides access to a row at a given physical offset that comes before the current row.

So using the sqlite3 command in Linux, the following should match the output listed in your question. The first 2 commands are only there to display proper output format.

```
sqlite> .mode columns
sqlite> .headers on
sqlite> CREATE TABLE data(id INT, record_id TEXT, price REAL);
sqlite> INSERT INTO data VALUES(1,"apple001",36.00);
sqlite> INSERT INTO data VALUES(67,"apple001",37.87);
sqlite> INSERT INTO data VALUES(765,"apple001",45.82);
sqlite> INSERT INTO data VALUES(892,"apple001",26.76);
sqlite> SELECT id, record_id, price, (price - LAG(price, 1) OVER (ORDER BY id)) AS difference FROM data;
id          record_id   price       difference
----------  ----------  ----------  ----------
1           apple001    36.0                  
67          apple001    37.87       1.87      
765         apple001    45.82       7.95      
892         apple001    26.76       -19.06
```

I hope this will save new users some time.

Finding the difference in rows in query using SQLite

Solution

Problem