Calculating the percentage change from the previous row

Question

I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column (%Grown) is given by:

-(1 - price of row 2 / price of row 1) * 100

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?

Answer 1

The best way I think would be to use the LAG() or LEAD() functions:

SELECT *, 
       - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;

With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in the question, you may want (ORDER BY Price DESC).

Answer 2

You can use Common Table expression,

WITH Data as (select  ROW_NUMBER() OVER (ORDER BY applicable_date) as RowId, date, qty, price, [value] from tbl
)
SELECT 
CurrentRow.date, CurrentRow.qty, CurrentRow.price, CurrentRow.[value], 'Your calculation here for Growth'
FROM Data as CurrentRow
LEFT OUTER JOIN Data as NextRow ON NextRow.RowId = CurrentRow.RowId + 1
WHERE NextSlice.RowId IS NOT NULL

I Hope it helps.