Longest Meeting-Free Block per Employee and Day
Problem statement
A workplace-analytics team wants to know how much uninterrupted focus time people get. For every employee on every business day, find the longest stretch inside business hours during which the employee is not in any meeting.
Tables (SQLite dialect; timestamps are UTC text 'YYYY-MM-DD HH:MM:SS' with whole seconds):
employees(employee_id INT) -- one row per employee
business_days(work_date TEXT, start_at TEXT, end_at TEXT) -- one row per day, start_at < end_at
meeting_attendance(employee_id INT, meeting_id TEXT, join_time TEXT, leave_time TEXT)
Rules:
- Every interval is half-open,
[start, end). Attendance rows satisfyjoin_time < leave_timeand reference valid employees. - Clip each attendance interval to the day's
[start_at, end_at); ignore it if the clipped interval is empty. One attendance row can overlap several business days, and is clipped to each one separately. - Rows can be duplicated or overlap (someone re-joins a meeting, or is double-booked). Merge overlapping or touching occupied intervals before measuring gaps.
- Free gaps include the stretch before the first meeting, between meetings and after the last meeting. An employee with no meetings that day has the whole window free; one occupied for the whole window gets 0.
Return employee_id, work_date, longest_free_seconds (an integer), ordered by employee_id, then work_date. Write one query (a WITH ... SELECT is fine).
Example
employees: 1, 2
business_days: ('2026-03-02', '2026-03-02 09:00:00', '2026-03-02 17:00:00')
meeting_attendance: (1, 'm1', '2026-03-02 09:30:00', '2026-03-02 10:30:00')
(1, 'm2', '2026-03-02 10:00:00', '2026-03-02 11:00:00')
output:
1 | 2026-03-02 | 21600
2 | 2026-03-02 | 28800
Employee 1 is busy from 09:30 to 11:00 after merging, so the longest free block is 11:00-17:00. Employee 2 has no meetings and must still appear, with the full 8 hours.
Show hint
Which rows must the output contain even if the attendance table has nothing for them?
Relevant roles: ML Engineer and Data Scientist.