mysql query items with the largest price increase

Question

I have a table 'item_prices' with:

resource_id, avg_price, time_stamp, samples

Items gets a new average price inserted into the table every day. Old averages are not deleted.

How can I query the 10 items with the highest "percent increase in price" since yesterdays average? I would also like to check that the samples is > 10 to ensure accuracy.

to clarify "percent increase in price":

percent_increase = (todays_avg_price - yesterdays_avg_price) / yesterdays_avg_price

example

resource_id |  avg_price |  time_stamp |  samples

    1           450         1380526003     12
    2           650         1380526002     2
    3           980         1380526001     68

    1           400         1380440003     24
    2           700         1380440002     13
    3           400         1380440001     38

    1           900         1380300003     11
    2           250         1380300002     8
    3           300         1380300001     4

returns

resource id  |  percent_increase

     3              1.45    
     1              0.125

Please, provide some data and sample of desired result – Alma Do Sep 30 '13 at 06:27 — Alma Do, Sep 30 '13 at 06:27
ok, updated with an example. – Steven Baltay Sep 30 '13 at 06:47 — Steven Baltay, Sep 30 '13 at 06:47
Can you provide the schema for the table? – Nikhil Sep 30 '13 at 06:49 — Nikhil, Sep 30 '13 at 06:49
all the rows are INT(15) – Steven Baltay Sep 30 '13 at 06:55 — Steven Baltay, Sep 30 '13 at 06:55

score 1 · Accepted Answer · answered Oct 01 '13 at 16:27

1

select  
today.resource_id, 
(today.avg_price - yesterday.avg_price) / yesterday.avg_price as percent_increase
from 
item_prices today, 
item_prices yesterday
where today.resource_id = yesterday_resource_id
and   DATE(FROM_UNIXTIME(today.timestamp)) = $today
and   DATE(FROM_UNIXTIME(yesterday.timestamp)) = $yesterday
order by percent_increase desc 
limit 10

This is a self join; apologies for the archaic join syntax, it's a bad habit I find hard to shake.

answered Oct 01 '13 at 16:27

Neville Kuyt

29,247
1
37
52

Ok Neveille thats really helpfull! What do I replace $today and $yesterday with? also im not sure if this matters but the timestamps for each day arent all the same, but they're within a 10 minute window – Steven Baltay Oct 01 '13 at 22:19
got it working! I used: and today.timestamp > UNIX_TIMESTAMP() - 85400 – Steven Baltay Oct 02 '13 at 03:22

score -1 · Answer 2 · edited Sep 30 '13 at 06:53

-1

Please try the query below..

  select top 1 resource_id,avg_price from item_Prices 
  group by resource_id,avg_price
  having count(resource_id,avg_price)>
  10 order by time_stamp

edited Sep 30 '13 at 06:53

Nadeem_MK

7,533
7
50
61

answered Sep 30 '13 at 06:31

Sai Avinash

4,683
17
58
96

1

He is using MySQL. Top 1 I believe is MSSQL syntax. – Nikhil Sep 30 '13 at 06:48

mysql query items with the largest price increase

2 Answers2