Calculating the price difference between two days

Question

I have the following table called Stores:

Date        |  Store  |  Price
2018-05-02  |  ABC    |  0.91
2018-05-02  |  DEF    |  0.81
2018-05-03  |  ABC    |  0.92
2018-05-03  |  DEF    |  0.83
2018-05-05  |  ABC    |  0.91
2018-05-05  |  DEF    |  0.85

I am trying to write a query that for a given store, will output the price, the price of the previous day, as well as the difference in price between two days and the price gain (which is a percentage). The output should look like the following:

 Date        |  Store  |  Price  |  PrevPrice  |  Change  |  Gain
 2018-05-03  |  ABC    |  0.92   |  0.91       |  0.01    |  1.086956522
 2018-05-05  |  ABC    |  0.91   |  0.92       |  -0.01   |  -1.098901099
 2018-05-03  |  DEF    |  0.83   |  0.81       |  0.02    |  2.409638554
 2018-05-05  |  DEF    |  0.85   |  0.83       |  0.02    |  2.352941176

The first date should not be in the output since it doesn't have a preceding date. I have the following query which gets the PrevPrice using lag():

select * 
from (
    select "Date", store, price, lag(price) over (partition by code order by "Date") as PrevPrice from Stores
) s where PrevPrice is not null;

I am not sure how I can calculate the price difference between two days or the price gain. More specifically, I don't know what methods I can use the calculate the price difference. Any insights are appreciated.

Answer 1

Almost there. Just subtract lag(price) from price:

SELECT Date, Store, Price, PrevPrice, Price - PrevPrice AS Change, (Price - PrevPrice) / PrevPrice * 100 AS Gain
FROM (
    SELECT Date, Store, Price, LAG(Price) OVER (PARTITION BY Store ORDER BY Date) AS PrevPrice
    FROM t
) AS x
WHERE PrevPrice IS NOT NULL