add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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'),
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;


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