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.
Top comments (0)