SQL get change in values based on consecutive dates

Question

I want to get the change in Price from day to day. What SQL query will accomplish this?

Original Table

Date       Company    Price
---------------------------
1/4/2012   Apple      458
1/3/2012   Apple      462
1/2/2012   Apple      451
1/1/2012   Apple      450

Desired Table

Date       Company    Price   Day_Change
-------------------------------------
1/4/2012   Apple      458     -4    
1/3/2012   Apple      462     9
1/2/2012   Apple      451     1
1/1/2012   Apple      450     NULL

Answer 1

Join the table to itself to get yesterday's price for the company, then subtract it from today's price

select
    t1.date,
    t1.company,
    t1.price,
    t1.price - t2.price as day_change
from price_table t1
left join price_table t2 
    on t2.date = subdate(t1.date, 1)
    and t2.company = t1.company

After this you can add a normal where clause, eg where t1.date > subdate(current_date(), 7) to get the last seven day's prices

FYI day_change will be NULL if there isn't a row for yesterday's price

Answer 2

Another approach, will work even in non-contiguous dates:

Source data:

CREATE TABLE fluctuate
    (Date datetime, Company varchar(10), Price int);

INSERT INTO fluctuate
    (Date, Company, Price)
VALUES
    ('2012-01-04 00:00:00', 'Apple', 458),
    ('2012-01-03 00:00:00', 'Apple', 462),
    ('2012-01-02 00:00:00', 'Apple', 451),
    ('2012-01-01 00:00:00', 'Apple', 450),
    ('2012-01-01 00:00:00', 'Microsoft', 1),
    ('2012-01-03 00:00:00', 'Microsoft', 7),
    ('2012-01-05 00:00:00', 'Microsoft', 5),
    ('2012-01-07 00:00:00', 'Microsoft', 8),
    ('2012-01-08 00:00:00', 'Microsoft', 12);

Output:

DATE                       COMPANY             PRICE               DAY_CHANGE
January, 04 2012           Apple               458                 -4
January, 03 2012           Apple               462                 11
January, 02 2012           Apple               451                 1
January, 01 2012           Apple               450                 NULL
January, 08 2012           Microsoft           12                  4
January, 07 2012           Microsoft           8                   3
January, 05 2012           Microsoft           5                   -2
January, 03 2012           Microsoft           7                   6
January, 01 2012           Microsoft           1                   NULL

Query:

select 

date, 
company, 
price, 
day_change

from
(    
  select 

     case when company <> @original_company then 
         -- new company detected, 
         -- reset the original price based on the new company
         @original_price := null
     end,
    f.*,
    price - @original_price as day_change,
    (@original_price := price),
    (@original_company := company)


  from fluctuate f

  cross join
  (
    select 
     @original_price := null,
     @original_company := company
     from fluctuate 
     order by company, date limit 1
  )
  as zzz

  order by company, date 

) as yyy
order by company, date desc

Source: http://www.sqlfiddle.com/#!2/56de3/3