MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (
  id integer, 
  some_date date, 
  value1 integer, 
  value2 float
  );
insert into table1 values 
(1, '2023-03-01', 1200, 3.2),
(1, '2023-03-15', 1400, 2.4),
(1, '2023-03-31', 2000, 2.8),
(2, '2023-03-01', 850, 4),
(2, '2023-03-15', 1250, 4.1),
(2, '2023-03-31', 3300, 3.6), 
(1, '2023-05-01', 10, 1.5), 
(2, '2023-05-02', 15, 2.3), 
(2, '2023-05-15', 20, 2.0);

Records: 9  Duplicates: 0  Warnings: 0


select 
 id, 
 some_date, 
 value1,  
 value2, 
 max(value1) over (partition by id, substring(some_date, 1, 7)) - 
 min(value1) over (partition by id, substring(some_date, 1, 7)) as val1_diff, 
 max(value2) over (partition by id, substring(some_date, 1, 7)) as max_val2
from table1


id
some_date
value1
value2
val1_diff
max_val2


1
2023-03-01
1200
3.2
800
3.2

1
2023-03-15
1400
2.4
800
3.2

1
2023-03-31
2000
2.8
800
3.2

1
2023-05-01
10
1.5
0
1.5

2
2023-03-01
850
4
2450
4.1

2
2023-03-15
1250
4.1
2450
4.1

2
2023-03-31
3300
3.6
2450
4.1

2
2023-05-02
15
2.3
5
2.3

2
2023-05-15
20
2
5
2.3


select 
 id, 
 substring(some_date, 1, 7) as yyyy_mm, 
 max(value1) - min(value1) as val1_diff, 
 max(value2) as max_vale
from table1
group by id, substring(some_date, 1, 7) 


id
yyyy_mm
val1_diff
max_vale


1
2023-03
800
3.2

2
2023-03
2450
4.1

1
2023-05
0
1.5

2
2023-05
5
2.3

id	some_date	value1	value2	val1_diff	max_val2
1	2023-03-01	1200	3.2	800	3.2
1	2023-03-15	1400	2.4	800	3.2
1	2023-03-31	2000	2.8	800	3.2
1	2023-05-01	10	1.5	0	1.5
2	2023-03-01	850	4	2450	4.1
2	2023-03-15	1250	4.1	2450	4.1
2	2023-03-31	3300	3.6	2450	4.1
2	2023-05-02	15	2.3	5	2.3
2	2023-05-15	20	2	5	2.3