Unplanned Absence Streaks of Three Working Days

Hard
244.

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. 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

ColumnType
day_noint
work_datedate

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

ColumnTypeValues
emp_idint
work_datedate
statusenumpresent, wfh, leave, absent

Primary key (together): emp_id, work_date.

One row per employee per working day; work_date is always a date in WorkDay.

Example

The rows must come back in the order shown.

WorkDay

day_nowork_date
12025-03-06
22025-03-07
32025-03-10
42025-03-11
52025-03-12
62025-03-13

Attendance

emp_idwork_datestatus
72025-03-06absent
72025-03-07absent
72025-03-10absent
72025-03-11present
72025-03-12absent
82025-03-06absent
82025-03-07leave
82025-03-10absent
82025-03-11absent
92025-03-10absent
3 more rows

Output

emp_idstreak_startstreak_enddays
72025-03-062025-03-103
92025-03-102025-03-134
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
day_noint
work_datedate

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

ColumnType
emp_idint
work_datedate
statusenum(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_nowork_date
12025-03-06
22025-03-07
32025-03-10
42025-03-11
52025-03-12
62025-03-13

Attendance

emp_idwork_datestatus
72025-03-06absent
72025-03-07absent
72025-03-10absent
72025-03-11present
72025-03-12absent
82025-03-06absent
82025-03-07leave
82025-03-10absent
82025-03-11absent
92025-03-10absent
92025-03-11absent
92025-03-12absent
92025-03-13absent

Output

emp_idstreak_startstreak_enddays
72025-03-062025-03-103
92025-03-102025-03-134

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_start

Another 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 →