How to find the average difference between each days stock price for the last 2 days for each row

Question

I seem to be struggling with getting the average difference between the stock price of the last consecutive 2 days for each row and would appreciate any help towards this. I beleive there may be an easier way using Inner Joins but I have really struggled with it.

Here is my table


Symbol  Date    Price       Date        Price      Date     Price 
1250    2015-01-14  47.51   2015-01-13  48.30   2015-01-12  48.80

I am trying to calculate average percentage difference between the last 2 days of Prices for all the rows. My table currently only has data for Symbol, Date and Price and what I would like to out put is

Symbol  Date    Price       % Change Date        Price     % Change  Date     Price 
1250    2015-01-14  47.51    -1.63    2015-01-13    48.30   2.02   2015-01-12   48.80

Thanks for any help or guidance I can get on this!

obe obe · Accepted Answer · 2016-03-13T19:18:24

Try this:

SELECT
    subQuery1.Symbol,
    subQuery1.Date AS 'Date 1',
    subQuery1.Price AS 'Price 1',
    ROUND(((subQuery1.Price - subQuery2.Price) / subQuery2.Price) * 100, 2) AS '% Change',
    subQuery2.Date AS 'Date 2',
    subQuery2.Price AS 'Price 2',
    ROUND(((subQuery2.Price - subQuery3.Price) / subQuery3.Price) * 100, 2) AS '% Change',
    subQuery3.Date AS 'Date 3',
    subQuery3.Price AS 'Price 3'
FROM
    (SELECT Symbol, Date, Price FROM tbl) AS subQuery1,
    (SELECT Symbol, Date, Price FROM tbl) AS subQuery2,
    (SELECT Symbol, Date, Price FROM tbl) AS subQuery3
WHERE
    subQuery1.Symbol = subQuery2.Symbol
    AND subQuery2.Symbol = subQuery3.Symbol
    AND subQuery1.Date = subQuery2.Date + INTERVAL 1 DAY
    AND subQuery2.Date = subQuery3.Date + INTERVAL 1 DAY

The performance will not be great with a large data set as it involves three sub-queries, temporary tables, and no indexes...

How to find the average difference between each days stock price for the last 2 days for each row

1 Answers