How to find the delta between rows in PostgreSQL

Imagine we have the following data:

+--------------------------+----------+
|       "timestamp"        | "price"  |
+--------------------------+----------+
| "2021-10-14 21:40:00+00" | 57681.01 |
| "2021-10-14 21:35:00+00" | 57698.41 |
| "2021-10-14 21:30:00+00" | 57575.77 |
| "2021-10-14 21:25:00+00" | 57573.92 |
+--------------------------+----------+

And we want to calculate the delta between prices, to see how much the current price increased or decreased relative the the previous one. For that we can use lead().

Having the following SQL query:

SELECT
    timestamp,
    price,
    lead(price) OVER (ORDER BY timestamp DESC) AS previous_price
FROM
    prices;

We get this data:

+--------------------------+----------+------------------+
|       "timestamp"        | "price"  | "previous_price" |
+--------------------------+----------+------------------+
| "2021-10-14 21:40:00+00" | 57681.01 |         57698.41 |
| "2021-10-14 21:35:00+00" | 57698.41 |         57575.77 |
| "2021-10-14 21:30:00+00" | 57575.77 |         57573.92 |
| "2021-10-14 21:25:00+00" | 57573.92 |         NULL     |
+--------------------------+----------+------------------+

Dissecting a little bit the lead part we can see that we have to use the OVER keyword and tell it how to order the data, and over that data we will get the lead price field.

Now to get the relative change we need to apply the following formula:

(b - a) / a

Where b is the current value and a the previous one.

So we will end with the following query; note we are already multiplying by 100 to get it as a percentage.

SELECT
    timestamp,
    price,
    (( price - lead(price) OVER (ORDER BY timestamp DESC)) / lead(price) OVER (ORDER BY timestamp DESC)) * 100 as percentage_change
FROM
    prices;

Giving us the data we want:

+--------------------------+----------+--------------------------+
|       "timestamp"        | "price"  |   "percentage_change"    |
+--------------------------+----------+--------------------------+
| "2021-10-14 21:40:00+00" | 57681.01 | -0.030156810213661       |
| "2021-10-14 21:35:00+00" | 57698.41 | 0.213006269824962827     |
| "2021-10-14 21:30:00+00" | 57575.77 | 0.0032132604484808399359 |
| "2021-10-14 21:25:00+00" | 57573.92 | NULL                     |
+--------------------------+----------+--------------------------+

Hope it was useful!

P.S. Here is a more detailed explanation of the lead function.