Spells of three or more school days absent

Hard

Question

The attendance table has one row for each student on each school day: present is 1 if the student came and 0 if the student was absent. There are no rows for weekends or holidays, because there is no school on those days. Every student has a row for every school day in the table.

An absence spell is a run of school days, one after the other, in which the student was absent. School days one after the other means the next school day in the table, so a spell can go over a weekend: absent on Friday and absent on the next Monday is a spell of 2 days.

Find every absence spell of 3 school days or more. Return student_id, first_day (the first absent day of the spell), last_day (the last absent day) and days (the number of school days in the spell). Sort by student_id and then by first_day.

The order of the rows matters for this question.

gaps and islands
window function
row number
dense rank
consecutive rows
date
attendance

Read first: Lesson 49, SQL patterns for time: streaks, sessions, cohorts, funnels and rolling windows

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

attendance3 columns · 32 rowsnot run yet

CREATE TABLE attendance (student_id INTEGER NOT NULL, day TEXT NOT NULL, present INTEGER NOT NULL, PRIMARY KEY (student_id, day));

Example rows of the attendance table
student_id	day	present
1	2026-03-02	1
1	2026-03-03	0
1	2026-03-04	0
1	2026-03-05	1
1	2026-03-06	0
1	2026-03-09	0
1	2026-03-10	0
1	2026-03-11	1
2	2026-03-02	0
2	2026-03-03	0
2	2026-03-04	0
2	2026-03-05	0
2	2026-03-06	1
2	2026-03-09	1
2	2026-03-10	0
2	2026-03-11	0
3	2026-03-02	0
3	2026-03-03	1
3	2026-03-04	0
3	2026-03-05	1
3	2026-03-06	0
3	2026-03-09	1
3	2026-03-10	0
3	2026-03-11	1
4	2026-03-02	1
4	2026-03-03	1
4	2026-03-04	1
4	2026-03-05	1
4	2026-03-06	0
4	2026-03-09	0
4	2026-03-10	0
4	2026-03-11	0

About this SQL

This is the gaps and islands pattern. The classic trick is: number all the rows of a student in day order, number only the absent rows in day order, and subtract. The result is the same for all rows of one unbroken run and changes after a present day. A window function is calculated after WHERE, so a ROW_NUMBER written next to WHERE present = 0 numbers only the absent rows. Do not subtract calendar dates: a weekend is a jump of 3 days, which would cut the spell. SQLite compares ISO dates (2026-03-09) as text correctly.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready