CODEWITHSUNDEEP
mysql
llanato
llanato

Reputation: 2491

MySQL Percentage Change in Recent Product Price Updates

I want to display the 10 most recently updated products where their price has either gone up or down in price and to display their percentage of change whether +/- percent and to ignore products where the current low price and the previous low price are the same and also to ignore products that have no previous low price, for example a product that has only one price record.

price_table (price has gone up or down)

id      prod_id price   store   added
----------------------------------------------------
1000    462     2.00    4       2016-01-27 00:54:47
807     462     3.09    2       2016-01-22 15:06:20
720     462     3.49    2       2016-01-16 09:31:01
608     462     2.89    2       2016-01-15 14:09:58

Here we see that the lowest price was 2.89 so when 3.49 was inserted the price difference would have been +17.19% and then 3.09 was inserted so the difference from the previous low price of 3.49 was -12.94% and then the most recent low price of 2.00 was inserted was so now the new lowest price difference from 3.09 is -35.28%.

price   store   percent_change
-------------------------------
2.89    2       
3.49    2       +17.19%
3.09    2       -12.94%
2.00    4       -35.28%

The percent_change kept changing here as the first 3 prices were the only prices available and they were from the one store.

Constraints

price_table (lowest price never changes)

id      prod_id price   store   added
----------------------------------------------------
140     140     19.99   1       2016-01-15 14:09:30
394     140     20.99   2       2016-01-15 14:09:48
991     140     24.99   4       2016-01-27 00:54:47
1361    140     21.49   2       2016-01-29 19:53:30

As you can see in the above table, the lowest price is 19.99 as store 1 hasn't updated since it was added, there have been 3 other price entries but they have all been above the 19.99 and after this lowest price so this product has no previous lowest price to use so these products should be ignored.

price_table (lowest price the same across two stores)

id      prod_id price   store   added
----------------------------------------------------
1280    374     8.99    5       2016-01-29 00:45:59
1247    374     10.49   2       2016-01-27 18:13:33
803     374     10.99   2       2016-01-22 15:06:20
374     374     8.99    1       2016-01-15 14:09:44

Here you will see that the lowest price was 8.99 at first and the the most recent price was 8.99 as well, these products should be ignored.

Current Logic

((Current Lowest Price - Previous Lowest Price) / Previous Lowest Price) * 100

Current SQL

SELECT pr1.prod_id AS product
, (((pr1.price - (SELECT price
    FROM price
    ORDER BY price ASC
    LIMIT 1,1)) / pr1.price) * 100) AS diff
FROM price pr1
ORDER BY pr1.price ASC;

SQLFiddle

I just can't seem to get the logic right, any help greatly appreciated.

Upvotes: 2

Views: 154

Answers (1)

Fabricator
Fabricator

Reputation: 12772

Use user-defined variables @prev_prod_id and @prev_price to keep track of the previous prod_id and price.

Use the following query to get prod_ids that you want to filter out:

select distinct a.prod_id
from (
  select prod_id, min(price) price, min(added) added
  from price a
  group by prod_id) a
join price b on a.added = b.added and a.price = b.price

All together:

select a.prod_id, a.price, a.store, 
 @prev_prod_id, @prev_price,1-@prev_price/a.price,
  if(@prev_prod_id = a.prod_id, 1-@prev_price/a.price, null)
    *if(@prev_price:=a.price,1,1)
    *if(@prev_prod_id:=a.prod_id,1,1) diff
from price a
join (select @prev_prod_id:=null, @prev_price:=null) b
left join (
  select distinct a.prod_id
  from (
    select prod_id, min(price) price, min(added) added
    from price a
    group by prod_id) a
  join price b on a.added = b.added and a.price = b.price) c on a.prod_id = c.prod_id
where c.prod_id is null
order by a.prod_id, a.added;

fiddle

Upvotes: 1

Related Questions