2

enter image description here

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;

3 Answers 3

3

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

0
1

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 
1
  • @ bigchief - thank you for your response. The code above worked fine. Appreciate your help
    – PS_22
    Mar 30, 2023 at 7:15
-1

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;
2
  • 2
    Also, it seem like you are finding all the occurrences where a student enrolled for no subject. The OP only wants to count how many times that happened in a row and get the max value per student.
    – Andriy M
    Mar 26, 2023 at 16:02
  • Output expected is abc : 3 xyz: 4 - yes you are correct the bit "Student N.... is the explanation of the result - Hope the above clarifies
    – PS_22
    Mar 27, 2023 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.