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'),
    ('31', '100.2','2022-07-05'),
    ('32', '109',  '2022-07-05'),
    ('33', '104', '2022-07-05'),
    ('34', '106', '2022-07-05'),
    ('35', '108', '2022-07-05'),  
    ('36', '111', '2022-07-05'),  
    ('37', '118', '2022-07-05'),  
    ('38', '114', '2022-07-05'),  
    ('39', '117', '2022-07-05'),
    ('40', '118', '2022-07-05')  ; 
select * from demo ORDER BY date_rehearsal DESC, id ASC


id
col1_val
date_rehearsal


31
100
2022-07-05

32
109
2022-07-05

33
104
2022-07-05

34
106
2022-07-05

35
108
2022-07-05

36
111
2022-07-05

37
118
2022-07-05

38
114
2022-07-05

39
117
2022-07-05

40
118
2022-07-05

1
100
2022-07-01

2
102
2022-07-01

3
104
2022-07-01

4
106
2022-07-01

5
108
2022-07-01

6
111
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


select * from demo where date_rehearsal ="2022-07-05"


id
col1_val
date_rehearsal


31
100
2022-07-05

32
109
2022-07-05

33
104
2022-07-05

34
106
2022-07-05

35
108
2022-07-05

36
111
2022-07-05

37
118
2022-07-05

38
114
2022-07-05

39
117
2022-07-05

40
118
2022-07-05


select * from demo where date_rehearsal ="2022-07-01"


id
col1_val
date_rehearsal


1
100
2022-07-01

2
102
2022-07-01

3
104
2022-07-01

4
106
2022-07-01

5
108
2022-07-01

6
111
2022-07-01

7
118
2022-07-01

8
114
2022-07-01

9
117
2022-07-01

10
118
2022-07-01


select * from demo where date_rehearsal ="2021-07-01"


id
col1_val
date_rehearsal


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


select * from demo where date_rehearsal ="2020-07-01"


id
col1_val
date_rehearsal


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


/** FREELANCE STACKOVERFLOW ANSWER*/
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-05
16
13.56
16 (13.56%)

2022-07-01
15
12.82
15 (12.82%)

2021-07-01
12
11.76
12 (11.76%)

2020-07-01
0
0.00
0 (0.00%)


/** FREELANCE STACKOVERFLOW ANSWER*/
WITH cte AS (
  SELECT
  a.date_rehearsal,
  a.col1_val,
  ROW_NUMBER() OVER (PARTITION BY a.date_rehearsal ORDER BY a.date_rehearsal DESC, a.id ASC) 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-05
7
6.42
7 (6.42%)

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%)


/** FREELANCE Truong T. ANSWER*/

set @parameter := '2020-07-01,2021-07-01,2022-07-01,2022-07-05';

drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql = concat("insert into temp (val) values ('", replace(( select group_concat(distinct @parameter)), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;

drop temporary table if exists temp1;
create temporary table temp1 as
SELECT
  date_rehearsal,
  col1_val,
  ROW_NUMBER() over (PARTITION BY date_rehearsal ORDER BY date_rehearsal DESC) AS rn
FROM demo
where date_rehearsal in (select distinct(val) from temp);

drop temporary table if exists temp2;
create temporary table temp2 as
SELECT
  date_rehearsal,
  col1_val,
  ROW_NUMBER() over (PARTITION BY date_rehearsal ORDER BY date_rehearsal DESC) AS rn
FROM demo
where date_rehearsal in (select distinct(val) from temp);

drop temporary table if exists temp3;
create temporary table temp3 as
select a.date_rehearsal, round(max((a.col1_val - b.col1_val) / a.col1_val * 100), 2) as percent, max(a.col1_val - b.col1_val) as diff
from temp1 a
left join temp2 b on b.date_rehearsal = date_add(a.date_rehearsal, interval -1 year) and b.rn = a.rn
group by a.date_rehearsal;


drop temporary table if exists temp4;
create temporary table temp4 as
select a.date_rehearsal, (a.col1_val - b.col1_val) as diff, a.col1_val
from temp1 a
left join temp2 b on b.date_rehearsal = date_add(a.date_rehearsal, interval -1 year) and b.rn = a.rn;

select t.DATES, if(t.col1_val = '0', t.col1_val, concat(concat(t.col1_val, ' '), t.percent)) as BT
from (
	select x.date_rehearsal as DATES, if(y.col1_val is null, '0', cast(y.col1_val as char(100))) as col1_val, if(x.percent is null, '', concat(concat('(', concat(cast(x.percent as char(100)), '%')), ')')) as percent
	from temp3 x
	left join temp4 y on y.date_rehearsal = x.date_rehearsal and y.diff = x.diff
) t
order by t.DATES desc;


DATES
BT


2022-07-05
0

2022-07-01
117 (12.82%)

2021-07-01
102 (11.76%)

2020-07-01
0


/** FREELANCE Bupendra. ANSWER*/

with t1 as (
  SELECT date_rehearsal, col1_val, 
  ROW_NUMBER() over (PARTITION BY date_rehearsal ORDER BY date_rehearsal) AS rn
  FROM demo
  )
select tab2.date_rehearsal, #tab1.col1_val cv1, tab2.col1_val cv2,
#tab2.col1_val - tab1.col1_val cv_diff
coalesce(max(tab2.col1_val - tab1.col1_val), 0) max_diff 
from t1 tab2 left join t1 tab1
on tab2.date_rehearsal = date_add(tab1.date_rehearsal, interval 1 year)
and tab1.rn = tab2.rn
group by tab2.date_rehearsal
order by tab2.date_rehearsal desc


date_rehearsal
max_diff


2022-07-05
0

2022-07-01
15

2021-07-01
12

2020-07-01
0

id	col1_val	date_rehearsal
31	100	2022-07-05
32	109	2022-07-05
33	104	2022-07-05
34	106	2022-07-05
35	108	2022-07-05
36	111	2022-07-05
37	118	2022-07-05
38	114	2022-07-05
39	117	2022-07-05
40	118	2022-07-05
1	100	2022-07-01
2	102	2022-07-01
3	104	2022-07-01
4	106	2022-07-01
5	108	2022-07-01
6	111	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

Dates	max_col1_val_difference	max_col1_val_percent	max_dif_with_percentage
2022-07-05	16	13.56	16 (13.56%)
2022-07-01	15	12.82	15 (12.82%)
2021-07-01	12	11.76	12 (11.76%)
2020-07-01	0	0.00	0 (0.00%)

Dates	max_col1_val_difference	max_col1_val_percent	max_dif_with_percentage
2022-07-05	7	6.42	7 (6.42%)
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	BT
2022-07-05	0
2022-07-01	117 (12.82%)
2021-07-01	102 (11.76%)
2020-07-01	0