MySQL 8.0

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

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