MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE IF NOT EXISTS `demo` (
  `id` int(6) unsigned NOT NULL, 
  `col1_val` integer (200) NOT NULL,
  `date_rehearsal` date  NOT NULL,  
  PRIMARY KEY (`id` )
) DEFAULT CHARSET=utf8;


INSERT INTO `demo` (`id`, `col1_val`, `date_rehearsal`) VALUES
    ('1', '100.2','2022-07-01'),
    ('2', '102',  '2022-07-01'),
    ('3', '104',  '2022-07-01'),
    ('4', '106',  '2022-07-01'),
    ('5', '108',  '2022-07-01'),  
    ('6', '110.5','2022-07-01'),  
    ('7', '118',  '2022-07-01'),  
    ('8', '114',  '2022-07-01'),  
    ('9', '117',  '2022-07-01'),
    ('10','118',  '2022-07-01'),
    ('11', '100', '2021-07-01'),
    ('12', '102', '2021-07-01'),
    ('13', '104', '2021-07-01'),
    ('14', '106', '2021-07-01'),
    ('15', '108', '2021-07-01'),  
    ('16', '110', '2021-07-01'),  
    ('17', '112', '2021-07-01'),  
    ('18', '114', '2021-07-01'),  
    ('19', '116', '2021-07-01'),
    ('20', '118', '2021-07-01'),  
    ('21', '90',  '2020-07-01'),
    ('22', '100', '2020-07-01'),
    ('23', '103', '2020-07-01'),
    ('24', '105', '2020-07-01'),
    ('25', '107', '2020-07-01'),  
    ('26', '109', '2020-07-01'),  
    ('27', '111', '2020-07-01'),  
    ('28', '113', '2020-07-01'),  
    ('29', '116', '2020-07-01'),
    ('30', '118', '2020-07-01') ; 
WITH cte1 AS (
  SELECT
  a.date_rehearsal,
  a.col1_val,
  ROW_NUMBER() OVER (PARTITION BY a.date_rehearsal ORDER BY a.date_rehearsal DESC) AS rn
  FROM demo a
),
cte2 AS (
  SELECT
  b.date_rehearsal,
  b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn ORDER BY b.date_rehearsal DESC), b.col1_val) AS diff
  FROM cte1 b),
cte3 AS (
  SELECT
  b.date_rehearsal,
  b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn ORDER BY b.date_rehearsal DESC), b.col1_val) AS diff
  FROM cte2 b)
SELECT
  c.date_rehearsal AS 'Dates',
  MAX(c.diff) as 'max_col1_val_difference'
  FROM cte2 c
GROUP BY c.date_rehearsal
ORDER BY c.date_rehearsal DESC


Dates
max_col1_val_difference


2022-07-01
6

2021-07-01
10

2020-07-01
0


WITH cte AS (
  SELECT
  a.date_rehearsal,
  a.col1_val,
  ROW_NUMBER() OVER (PARTITION BY a.date_rehearsal ORDER BY a.date_rehearsal DESC) AS rn
  FROM demo a
)
SELECT
  c.date_rehearsal AS 'Dates',
  MAX(c.diff) as 'max_col1_val_difference',
  ROUND(MAX(c.diffPercent),2) as 'max_col1_val_percent',
  CONCAT(MAX(c.diff), ' (', ROUND(MAX(c.diffPercent),2), '%)') as 'max_dif_with_percentage'
  FROM (
     SELECT
     b.date_rehearsal,
     b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn ORDER BY b.date_rehearsal DESC), b.col1_val) AS diff,
     (((b.col1_val - COALESCE(LEAD(b.col1_val) OVER (PARTITION BY b.rn ORDER BY b.date_rehearsal DESC), b.col1_val))/b.col1_val)*100) AS diffPercent
     FROM cte b) c
GROUP BY c.date_rehearsal
ORDER BY c.date_rehearsal DESC


Dates
max_col1_val_difference
max_col1_val_percent
max_dif_with_percentage


2022-07-01
6
5.08
6 (5.08%)

2021-07-01
10
10.00
10 (10.00%)

2020-07-01
0
0.00
0 (0.00%)
Dates	max_col1_val_difference	max_col1_val_percent	max_dif_with_percentage
2022-07-01	6	5.08	6 (5.08%)
2021-07-01	10	10.00	10 (10.00%)
2020-07-01	0	0.00	0 (0.00%)