By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
ID INT(3) NOT NULL,
value INT(3) NOT NULL,
date DATE NOT NULL
);
INSERT INTO test (ID, value, date)
VALUES
(1, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(1, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
(1, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)),
(1, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)),
(2, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(2, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
(2, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)),
(3, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(4, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(5, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(3, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
(2, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)),
(4, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),
(3, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)),
(5, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY)),
(4, CEIL(RAND() * 10), DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY))
select * from test
| ID | value | date |
|---|---|---|
| 1 | 2 | 2020-12-08 |
| 1 | 5 | 2020-12-07 |
| 1 | 8 | 2020-12-06 |
| 1 | 7 | 2020-12-05 |
| 2 | 7 | 2020-12-08 |
| 2 | 6 | 2020-12-07 |
| 2 | 6 | 2020-12-06 |
| 3 | 4 | 2020-12-08 |
| 4 | 2 | 2020-12-08 |
| 5 | 5 | 2020-12-08 |
| 3 | 10 | 2020-12-07 |
| 2 | 4 | 2020-12-05 |
| 4 | 1 | 2020-12-07 |
| 3 | 9 | 2020-12-06 |
| 5 | 5 | 2020-12-08 |
| 4 | 7 | 2020-12-06 |
select id, oldest_value, newest_value
from (select t.*,
row_number() over (partition by id order by date) as seqnum,
first_value(value) over (partition by id order by date) as oldest_value,
first_value(value) over (partition by id order by date desc) as newest_value
from test t
) t
where seqnum = 1
order by newest_value / oldest_value
| id | oldest_value | newest_value |
|---|---|---|
| 1 | 7 | 2 |
| 4 | 7 | 2 |
| 3 | 9 | 4 |
| 5 | 5 | 5 |
| 2 | 4 | 7 |