Calculate percent increase/decrease from previous row value

Question

I have a table that looks something like this:

|date_start  | date_end    |amount | 
+------------+-------------+-------+
|2015-02-23  | 2015-03-01  |50     |
|2015-03-02  | 2015-03-08  |50     |
|2015-03-09  | 2015-03-15  |100    |
|2015-03-16  | 2015-03-22  |800    |
|2015-03-23  | 2015-03-29  |50     |

and I'd like to work out the percent increase/decrease for column amount, from the previous date. For example the result would be something like this,

|date_start  | date_end    |amount | perc_change | 
+------------+-------------+-------+-------------+
|2015-02-23  | 2015-03-01  |50     | 
|2015-03-02  | 2015-03-08  |50     | 0
|2015-03-09  | 2015-03-15  |100    | 50
|2015-03-16  | 2015-03-22  |800    | 700
|2015-03-23  | 2015-03-29  |50     | -750

I've searched and racked my brain for a couple of days now. Usually, I simply do this using server side code but now I need to contain it all within the query.

Answer 1

Try this:

 SELECT t.*,
 amount - (SELECT amount FROM transactions prev WHERE prev.date_end     < t.date_start ORDER BY date_start DESC LIMIT 1) AS changes
 FROM   transactions t