Longest Meeting-Free Business-Hours Block per Employee and Day

A company wants to measure uninterrupted time available for project work. Given meeting attendance intervals, return the longest meeting-free block within business hours for every employee on every supplied business day. Write one read-only PostgreSQL query over these tables: ```sql employees(employee_id BIGINT PRIMARY KEY) business_days(work_date DATE PRIMARY KEY, start_at TIMESTAMPTZ NOT NULL, end_at TIMESTAMPTZ NOT NULL) meeting_attendance(employee_id BIGINT NOT NULL, meeting_id TEXT NOT NULL, join_time TIMESTAMPTZ NOT NULL, leave_time TIMESTAMPTZ NOT NULL) ``` Return `employee_id`, `work_date`, and `longest_free_seconds` as a nonnegative integer, ordered by `employee_id`, then `work_date`. ### Constraints & Assumptions - The report supplies attendance columns but leaves the roster and business-hours calendar unspecified. The two supplementary fixture tables explicitly define those missing inputs for this SQL practice version. - Every listed employee is in scope for every supplied business day. Include employees/days with no meetings. - Each business interval satisfies `start_at < end_at`, has whole-second timestamps, and belongs to the stated `work_date` in the company's chosen calendar. Use those absolute boundaries directly rather than guessing a timezone from the date. - Attendance rows have `join_time < leave_time`, whole-second timestamps, and valid employee IDs. Duplicate or overlapping rows may exist, including multiple joins to one meeting. - Intervals are half-open. Clip each attendance interval to the business interval; ignore it if the clipped intersection is empty. - Merge overlapping or touching occupied intervals before measuring free gaps. Count gaps before the first meeting and after the last meeting as well as gaps between meetings. - Attendance may span multiple business days. Compute its intersection with each supplied day independently. - An employee with no occupied time on a day has the full business interval free; an employee occupied for the entire interval has zero free seconds. ### Examples If business hours are 09:00–17:00 and an employee attends 09:30–10:30 and 10:00–11:00, the merged occupied interval is 09:30–11:00. The longest free block is 11:00–17:00, or 21,600 seconds. If another employee has no meetings that day, return 28,800 seconds for that employee/day. Do not omit the row because attendance is absent. ```hint Compare with the furthest prior end A meeting can be contained inside an earlier longer meeting. When assigning occupied-interval groups, compare its start with the running maximum prior end rather than only the immediately preceding row's end. ```