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:

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.