I’d like to retrieve the results from my timetable where there are more than 3 consecutive gaps on a sigle day, for a class. I have managed to accomplish this for a solo class, but I’m unable to do it when combining with coupling classes.

• Solo class = A class that represents itself, example “A”, “B”
• Coupling class = A class that represents 2 or more classes, example “A, B”, “D, E, F”

When looking for consecutive gaps, I need to take into consideration that the class can be solo “A” or in a coupling “A, B”

My initial table structure:

CREATE TABLE classes (
  id INT AUTO_INCREMENT KEY,
  name VARCHAR,
);  

INSERT INTO classes (name) 
VALUES 
    ('A'),
    ('B'),
    ('A, B');
    
CREATE TABLE classes_coupling (
  id INT AUTO_INCREMENT KEY,
  class_id INT,
  class_coupling_id 
);  
    
INSERT INTO classes_coupling (class_id, class_coupling_id) 
VALUES 
    (3, 1),
    (3, 2);  

My timetable table structure:

CREATE TABLE timetable (
  id INT AUTO_INCREMENT KEY,
  day_id INT,
  timeslot_id INT,
  class_id INT
);

--- class_id 1 = A 
--- class_id 2 = A, B

INSERT INTO timetable (day_id, timeslot_id, class_id) 
VALUES 
    (1, 1, 1),
    (1, 2, 1),
    (1, 6, 1), --- Gap here, from 2 to 6 in class "A"
    (2, 1, 1),
    (2, 2, 1),
    (2, 4, 1),
    (3, 1, 1), --- From here to end gap shouldn't exist because the class_id = 2 is related with "A"
    (3, 2, 1), 
    (3, 3, 2), 
    (3, 4, 2), 
    (3, 7, 2), 
    (3, 8, 2), 
    (3, 9, 1);

Since I’m unable to tell that class_id = 2 is also related to class “A”, the query shows 2 results:

• Gap : day 1 : between 2 & 6
• Gap : day 3 : between 2 to 9

My query to check if the class has gaps:

SELECT 
    day_id,
    class_id,
    MIN(timeslot_id) AS start_timeslot,
    MAX(next_timeslot_id) - 1 AS end_timeslot,
    MAX(next_timeslot_id) - MIN(timeslot_id) - 1 AS gap_size
FROM (
    SELECT 
        day_id,
        class_id,
        timeslot_id,
        LEAD(timeslot_id) OVER (PARTITION BY day_id, class_id ORDER BY timeslot_id) AS next_timeslot_id
    FROM 
        timetable
) AS subquery
WHERE 
    (next_timeslot_id - timeslot_id > 3 OR next_timeslot_id IS NULL)
GROUP BY 
    day_id, class_id, next_timeslot_id
HAVING 
    MAX(next_timeslot_id) - MIN(timeslot_id) > 3 AND class_id = 1
ORDER BY 
    day_id, class_id, start_timeslot;

In the end, I’m looking for the following simple output:

day_id | class_id 
1      | 1

Basically, classes grouped by day where they have 3 consecutive timeslots empty. How can I achieve this?