Coding questions · Intuit

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

Find each employee’s longest meeting-free business-hours interval using clipped attendance, merged overlaps, complete day coverage, and SQL window logic.

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.

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.