Q:

SQL query to get biggest price decreases

Need some help with a complicated SQL query.
We have the table pricechanges with the columns id, productid, date, price
For each productid, there are a number of rows with date = a timestamp and price = the price at the time of the timestamp.
Like this:
productid, date, price
3, 17/5-2016 22:00:00, 100
3, 18/5-2016 22:00:00, 120
3, 19/5-2016 22:00:00, 140
3, 20/5-2016 22:00:00, 120
3, 21/5-2016 22:00:00, 140

I would like to get the 10 products with the biggest price decrease in the last 7 days.
Is that possible somehow?
Thanks!
PS: It's a MySQL database.

A:

IF i get the logic and then tables right the try the following:
select d.productID from 
 (  
 select a.productid, (a.price-b.price) as priceDiff
 from products a 
 inner join products b 
 on a.productID=b.productID
 and Date(a.date)=Date(b.date)+7 
  ) d
order by priceDiff
Limit 10

