High School Attendance

Three-plus consecutive absences — gaps-and-islands done right

Find students absent for 3 or more consecutive days; return student_id and the start_date of each run. The earlier solution set its grouping key to s_rank - ROW_NUMBER() where s_rank was itself a ROW_NUMBER() over the same order — so the key was always 0 and never separated runs. The correct islands trick subtracts the row number from the date:

WITH absents AS (
    SELECT student_id, attendance_date,
           ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY attendance_date) AS rn
    FROM Attendance
    WHERE status = 'Absent'
),
islands AS (
    SELECT student_id, attendance_date,
           DATE_SUB(attendance_date, INTERVAL rn DAY) AS grp   -- constant within a consecutive run
    FROM absents
)
SELECT student_id, MIN(attendance_date) AS start_date
FROM islands
GROUP BY student_id, grp
HAVING COUNT(*) >= 3
ORDER BY student_id, start_date;

Traced on the sample

student	absent dates	rn	date − rn = grp	run len
1	11-01, 11-02, 11-03	1,2,3	10-31 (all)	3 ≥ 3 ✓ start 11-01
2	11-02, 11-03, 11-04	1,2,3	11-01 (all)	3 ≥ 3 ✓ start 11-02

Output {(1, 2020-11-01), (2, 2020-11-02)} — matches the expected result. Student 3 (never absent) drops out.

Takeaways

• The islands key must subtract the row number from the date, not from another identical row number.
• MIN(attendance_date) per island gives the run's start; HAVING COUNT(*) >= 3 keeps long runs.

---

Re-authored for correctness for this guide (the prior grouping key was always 0). Pattern: consecutive-absence islands. See also: Active Users, Window Functions.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — High School Attendance

Why this concept exists (judgment layer)

Gaps-and-islands: consecutive absences. The load-bearing trick is date − row_number as island key — using rn − rn collapses everything to zero.

Mental model (install this intuition)

Filter to Absent → number rows per student by date → grp = date − rn days is constant on consecutive calendar runs → GROUP BY student, grp HAVING COUNT >= 3 → MIN(date) is start.

Worked example with numbers or traced steps

Student1: 11-01,11-02,11-03  rn 1,2,3 → date-rn = 10-31 all → len 3 → start 11-01
Student2: 11-02..11-04 → grp 11-01 → start 11-02
Broken key: s_rank - rn where both are ROW_NUMBER same order → always 0
Gap day: 11-01,11-02,11-04 → two islands (len 2 and 1) — no output if threshold 3

When NOT to use / named alternative

If 'consecutive' means business days only, subtract using a calendar table, not raw date−rn. If absences are event timestamps not daily grain, bucket to day first.

Failure mode & ops fingerprint

Fingerprint: always-zero group key from rn−rn; weekend gaps splitting islands unexpectedly; including Present rows in the window numbering.

Hostile-panel drills (defend the decision)

Q1. Why date − rn works?
Model answer: On consecutive days, date and rn both increase by 1, so difference is invariant; a gap changes date more than rn.

Q2. How to return run length and end date?
Model answer: SELECT MIN(date), MAX(date), COUNT(*) … GROUP BY student_id, grp HAVING COUNT(*)>=3.

Q3. Pattern family?
Model answer: Gaps and islands (island key = attribute − ROW_NUMBER).

I just read a lesson on **High School Attendance** (Databases) and want to truly understand it. Explain High School Attendance from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **High School Attendance** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **High School Attendance** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **High School Attendance** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.