Find out students max no of times not enrolled consecutively in a subject

Question

This is my sample data set. Looking for code in MySQL

Desired output

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;

Answer 1

There are various techniques for finding islands. One of them indeed involves two ROW_NUMBER calls, although not quite the way you have been doing it.

WITH
  partitioned AS
  (
    SELECT
      *
    , ROW_NUMBER() OVER (PARTITION BY Student          ORDER BY Year ASC, Term ASC) -
      ROW_NUMBER() OVER (PARTITION BY Student, Subject ORDER BY Year ASC, Term ASC) AS IslandID
    FROM
      test_student
  )
, counted AS
  (
    SELECT
      Student
    , COUNT(*) AS Times
    FROM
      partitioned
    WHERE
      Subject = ''
    GROUP BY
      Student
    , IslandID
  )
SELECT
  Student
, MAX(Times) AS Times
FROM
  counted
GROUP BY
  Student
;

Student	Times
abc	3
XYZ	4

fiddle

Answer 2

Here is a way to get the desired result without window functions:

select sq.student, max(sq.gap) as gap
from (
    select t1.student,
    min((t2.year + ((cast(substring(t2.term,2,1) as decimal(6,2))-1)/4))*4) - (t1.year + ((cast(substring(t1.term,2,1) as decimal(6,2))-1)/4))*4 as gap
    from test_student t1
    left outer join test_student t2 on
        t2.student = t1.student and
        t2.Subject <> '' and
        (t2.year = t1.year and t2.Term > t1.Term or t2.year > t1.year)
    where t1.Subject = ''
    group by t1.student, t1.year, t1.Term
) as sq
group by sq.student

This assumes that there is exactly one record for each student/year/term. You could accommodate for this with some extra subquery/grouping if needed.

The thought process is:

1. Get all the year/terms where a student is not enrolled
2. Find the future year/terms where that same student is enrolled (join on itself)
3. group and find the minimum future term enrolled = next term enrolled
4. subtract each row (year/term) from the min (next term enrolled) to get the gap
5. group by student and get the max gap

I had to convert year+term into a "counter" in order to do some math to find out the size of the gap when joining the table onto itself. This could be simplified with a counting/lookup table. Here is an example of the counter:

select *, (t1.year + ((cast(substring(t1.term,2,1) as decimal(6,2))-1)/4))*4 from test_student t1 order by student, year, term 

Answer 3

I suppose you are on a MySQL 8.x. The exists function makes this ideal for MySQL 5.7 onwards

select concat(sum(missed), '( Student ' , student, ' didn''t enroll for 
any subject consecutively for ',group_concat(dtl_missed separator ' and 
'), ')') as missed_list from(
SELECT count(*) as missed, student,concat(group_concat(term), ' in ', 
year) as dtl_missed  FROM enrollments e2 where exists
(
SELECT * FROM enrollments e1 WHERE e2.student=e1.student and 
e2.year>=e1.year and e2.subject is null
) group by student,year
) as see_missed group by student;