Coding questions · Agoda
Find the Longest Calendar-Day Streak with All Employees Present
Identify dates when every employee is present and compute the longest consecutive streak, treating missing attendance as a gap.
Find the longest run of consecutive calendar days on which every employee was present.
Input Tables
employees(employee_id INTEGER PRIMARY KEY) contains at least one employee.
daily_attendance(employee_id INTEGER, attendance_date DATE, status TEXT) has a primary key on (employee_id, attendance_date). All fields are non-null, employee identifiers reference employees, and status is either Y for present or N for absent.
Output Contract
Write one read-only PostgreSQL query returning exactly one row with one integer column, longest_streak.
A date qualifies only when every employee in employees has a Y record for that date. A missing record counts as not confirmed present, so that date does not qualify. Return the length of the longest consecutive-date run of qualifying dates, or 0 if none qualify.
Example
There are two employees. Both have Y records on January 1 and January 2. On January 3 one employee has N. Both have Y on January 4.
Expected result:
| longest_streak |
|---|
| 2 |
Constraints and Clarifications
- The employee population is the fixed set in
employeesthroughout this exercise. - Consecutive means consecutive calendar dates, including weekends and month boundaries.
- A date with no attendance records is a gap, even if the nearest recorded dates each qualify.
- The attendance table may be empty, in which case return
0. - Do not infer that all employees attended merely because all records present for a date are
Y; some employees may be missing.
Hint: Establish qualifying dates before finding a streak
First compare each day's confirmed attendance with the full employee population. Then measure adjacency between the resulting calendar dates.
Longest Streak of Full-Attendance Days
Table employees
| column | type | constraints |
|---|---|---|
| employee_id | INTEGER | PRIMARY KEY |
Sample data:
| employee_id |
|---|
| 101 |
| 102 |
| 103 |
Table daily_attendance
| column | type | constraints |
|---|---|---|
| employee_id | INTEGER | NOT NULL |
| attendance_date | DATE | NOT NULL |
| status | TEXT | NOT NULL |
Sample data:
| attendance_date | employee_id | status |
|---|---|---|
| 2024-01-27 | 101 | Y |
| 2024-01-27 | 102 | Y |
| 2024-01-27 | 103 | Y |
| 2024-01-28 | 101 | Y |
| 2024-01-28 | 102 | Y |
| 2024-01-28 | 103 | Y |
| 2024-01-29 | 101 | Y |
| 2024-01-29 | 102 | Y |
| 2024-01-29 | 103 | N |
| 2024-01-30 | 101 | Y |
| 2024-01-30 | 102 | Y |
| 2024-01-30 | 103 | Y |
| 2024-01-31 | 101 | Y |
| 2024-01-31 | 102 | Y |
| 2024-01-31 | 103 | Y |
| 2024-02-01 | 101 | Y |
| 2024-02-01 | 102 | Y |
| 2024-02-01 | 103 | Y |
| 2024-02-02 | 101 | Y |
| 2024-02-02 | 102 | Y |
| 2024-02-03 | 101 | Y |
| 2024-02-03 | 102 | Y |
| 2024-02-03 | 103 | Y |
| 2024-02-04 | 101 | Y |
| 2024-02-04 | 102 | Y |
| 2024-02-04 | 103 | Y |
| 2024-02-06 | 101 | Y |
| 2024-02-06 | 102 | Y |
| 2024-02-06 | 103 | Y |
| 2024-02-07 | 101 | Y |
| 2024-02-07 | 102 | Y |
| 2024-02-07 | 103 | Y |
Expected output
| longest_streak |
|---|
| 3 |
Hints
- Establish qualifying dates before finding a streak: first compare each day's confirmed attendance with the full employee population.
- Then measure adjacency between the resulting calendar dates.