Find the Longest Calendar-Day Streak with All Employees Present

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:

Constraints and Clarifications

• The employee population is the fixed set in employees throughout 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.