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: | longest_streak | | --- | | 2 | ### 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. ```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. ```