CODEWITHSUNDEEP
mysqlsql
daltonfury42
daltonfury42

Reputation: 3734

SQL: Query by percentage change in a timerange

I have a table with the prices of products recorded at irregular intervals of time:

prod_id   | price      | timestamp
p1        | 10         | 01-01-2018
p1        | 25         | 03-01-2018
p1        | 15         | 05-01-2018
p2        | 40         | 03-01-2018
p2        | 80         | 04-01-2018
p2        | 90         | 08-01-2018
p3        | 150        | 03-01-2018
p3        | 200        | 05-01-2018

Given a time range, and a threshold, I want to query the table and get the products whose percentage increase in the timeperiod is greater than the input threshold.

For example, if the time range is 01-01-2018 to 06-01-2018, the percentage increase for p1, p2, and p3 are:

p1 -> 50%
p2 -> 100%
p3 -> 33.33%

So if the threshold is 40%, the response should be 

prod_id   |
p1
p2

Is it possible to do this using SQL?

Upvotes: 0

Views: 64

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try this:

Sample data:

create table tbl (prod_id char(2), price int, `timestamp` date);
insert into tbl values
('p1', 10, '2018-01-01'),
('p1', 25, '2018-01-03'),
('p1', 15, '2018-01-05'),
('p2', 40, '2018-01-03'),
('p2', 80, '2018-01-04'),
('p2', 90, '2018-01-08'),
('p3', 150, '2018-01-03'),
('p3', 200, '2018-01-05');

T-SQL:

set @start = '2018-01-01', @end = '2018-01-06', @threshold = 0.4;

select a.prod_id, (c.price - b.price)/b.price from (
    select prod_id, min(`timestamp`) `min`, max(`timestamp`) `max` from tbl
    where `timestamp` between @start and @end
    group by prod_id
) a join tbl b on a.prod_id = b.prod_id and a.`min` = b.`timestamp`
    join tbl c on a.prod_id = c.prod_id and a.`max` = c.`timestamp`
where (c.price - b.price)/b.price > @threshold;

Upvotes: 1

Related Questions