This is my sample data set. Looking for code in MySQL
Desired output abc : 3 (Student abc didn't enroll for any subject consecutively for T3,T4 in 2021 and then for T1 in 2022) xyz : 4 (Student xyz didn't enroll for any subject consecutively for T3,T4 in 2021 and for T1,T2 in 2022)
| Student | Times |
|---|---|
| abc | 3 (Student abc didn't enroll for any subject consecutively for T3,T4 in 2021 and then for T1 in 2022) |
| xyz | 4 (Student xyz didn't enroll for any subject consecutively for T3,T4 in 2021 and for T1,T2 in 2022) |
Code:
create table test_student (student varchar(25), Term char (2),
year int, Subject varchar(20));
insert into test_student
values ("abc","t1","2021","sub1"),
("abc","t2","2021","sub2"),
("abc","t3","2021",""),
("abc","t4","2021",""),
("abc","t1","2022",""),
("abc","t2","2022","sub3"),
("abc","t3","2022",""),
("abc","t4","2022",""),
("abc","t1","2023","sub4"),
("abc","t2","2023",""),
("abc","t3","2023","sub5"),
("XYZ","t1","2021","sub1"),
("XYZ","t2","2021","sub2"),
("XYZ","t3","2021",""),
("XYZ","t4","2021",""),
("XYZ","t1","2022",""),
("XYZ","t2","2022",""),
("XYZ","t3","2022","sub3"),
("XYZ","t4","2022",""),
("XYZ","T1","2023",""),
("XYZ","t2","2023","sub4"),
("XYZ","t3","2023","sub5");
The code below gives me output of abc: 3 xyz: 3
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY student, subject ORDER BY term) AS term_num
FROM test_student
)
SELECT student, MAX(gap_length) AS max_gap
FROM (
SELECT student,
term_num - ROW_NUMBER() OVER (PARTITION BY student, year ORDER BY term) AS gap_length
FROM cte
WHERE subject = ''
) AS gaps
GROUP BY student;
