Unplanned Absence Streaks of Three Working Days
Under the attendance policy, an employee who is absent (no punch, no approved leave) on three or more consecutive working days gets a call from HR.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #244
Problem statement
Under the attendance policy, an employee who is absent (no punch, no approved leave) on three or more consecutive working days gets a call from HR. Working days are listed in WorkDay, numbered in order, so a weekend or holiday between two working days does not break a streak; an approved leave or a present/wfh day does.
Return every streak of at least three consecutive working days of absent status, with emp_id, streak_start, streak_end (the first and last dates) and days (working days in the streak). Order by emp_id, then streak_start.
Tables
Table: WorkDay
| Column | Type |
|---|---|
| day_no | int |
| work_date | date |
Primary key: day_no.
One row per working day of the office calendar; day_no rises by 1 from one working day to the next, skipping weekends and holidays.
Table: Attendance
| Column | Type |
|---|---|
| emp_id | int |
| work_date | date |
| status | enum(present, wfh, leave, absent) |
Primary key: emp_id, work_date.
One row per employee per working day; work_date is always a date in WorkDay.
Examples
Example 1
WorkDay
| day_no | work_date |
|---|---|
| 1 | 2025-03-06 |
| 2 | 2025-03-07 |
| 3 | 2025-03-10 |
| 4 | 2025-03-11 |
| 5 | 2025-03-12 |
| 6 | 2025-03-13 |
Attendance
| emp_id | work_date | status |
|---|---|---|
| 7 | 2025-03-06 | absent |
| 7 | 2025-03-07 | absent |
| 7 | 2025-03-10 | absent |
| 7 | 2025-03-11 | present |
| 7 | 2025-03-12 | absent |
| 8 | 2025-03-06 | absent |
| 8 | 2025-03-07 | leave |
| 8 | 2025-03-10 | absent |
| 8 | 2025-03-11 | absent |
| 9 | 2025-03-10 | absent |
| 9 | 2025-03-11 | absent |
| 9 | 2025-03-12 | absent |
| 9 | 2025-03-13 | absent |
Output
| emp_id | streak_start | streak_end | days |
|---|---|---|---|
| 7 | 2025-03-06 | 2025-03-10 | 3 |
| 9 | 2025-03-10 | 2025-03-13 | 4 |
How to solve Unplanned Absence Streaks of Three Working Days
Two things make this harder than it looks. First, consecutive means consecutive working days: a streak from Friday into Monday is unbroken. The calendar table solves that — join each attendance row to WorkDay and reason about day_no, which rises by exactly 1 from one working day to the next. Second, a run must be broken by any non-absent status, so keep only status = 'absent' rows before looking for runs; a leave day then leaves a hole in the day numbers.
What remains is gaps and islands. Number each employee's absent days with ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY day_no). Along a run of consecutive day numbers both values rise by one each step, so day_no - ROW_NUMBER() is constant; any hole changes it. Grouping by employee and that key gives one group per streak: MIN/MAX of the dates are its ends, COUNT(*) its length, and HAVING COUNT(*) >= 3 keeps the ones that trigger a call.
The alternative marks a row as the start of a streak when the previous absent day (LAG) is not day_no - 1, then numbers the streaks with a running SUM of the start flags — an explicit ROWS frame keeps the running total exact. Both are a sort per employee plus a grouping.
Reference solution (MySQL)
WITH absent AS (
SELECT a.emp_id, w.day_no, w.work_date,
w.day_no - ROW_NUMBER() OVER (PARTITION BY a.emp_id ORDER BY w.day_no) AS grp
FROM Attendance a
JOIN WorkDay w ON w.work_date = a.work_date
WHERE a.status = 'absent'
)
SELECT emp_id, MIN(work_date) AS streak_start, MAX(work_date) AS streak_end, COUNT(*) AS days
FROM absent
GROUP BY emp_id, grp
HAVING COUNT(*) >= 3
ORDER BY emp_id, streak_startAnother way
WITH absent AS (
SELECT a.emp_id, w.day_no, w.work_date,
CASE WHEN LAG(w.day_no) OVER (PARTITION BY a.emp_id ORDER BY w.day_no) = w.day_no - 1 THEN 0 ELSE 1 END AS is_start
FROM Attendance a JOIN WorkDay w ON w.work_date = a.work_date
WHERE a.status = 'absent'
), labelled AS (
SELECT emp_id, work_date,
SUM(is_start) OVER (PARTITION BY emp_id ORDER BY day_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS streak_no
FROM absent
)
SELECT emp_id, MIN(work_date) AS streak_start, MAX(work_date) AS streak_end, COUNT(*) AS days
FROM labelled
GROUP BY emp_id, streak_no
HAVING COUNT(*) >= 3
ORDER BY emp_id, streak_start← Overlapping Approved Leave Within a Team · Median CTC for Each Job Level →