DEV Community 👩‍💻👨‍💻

Daniel Cruz
Daniel Cruz

Posted on

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 |
+--------------------------+----------+
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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     |
+--------------------------+----------+------------------+
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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                     |
+--------------------------+----------+--------------------------+
Enter fullscreen mode Exit fullscreen mode

Hope it was useful!

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

Top comments (0)

50 CLI Tools You Can't Live Without

The top 50 must-have CLI tools, including some scripts to help you automate the installation and updating of these tools on various systems/distros.