Microsoft: Teams Meetings That Ran Past Their Booked Slot
A Microsoft Teams product analyst studying meeting overload wants to know which meetings ran longer than the slot their organiser booked.
- Difficulty: Easy
- Topics: Dates
- Dialect: MySQL
- Problem: #774
Problem statement
A Microsoft Teams product analyst studying meeting overload wants to know which meetings ran longer than the slot their organiser booked.
For every meeting that has ended and lasted strictly longer than booked_minutes, return meeting_id, organizer and overrun_minutes — the minutes between started_at and ended_at, minus the booked minutes. Meetings still in progress have ended_at NULL and are left out. Order by overrun_minutes descending, then meeting_id ascending.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: TeamsMeeting
| Column | Type |
|---|---|
| meeting_id | int |
| organizer | varchar |
| booked_minutes | int |
| started_at | datetime |
| ended_at | datetime |
Primary key: meeting_id.
One row per scheduled meeting. Times are whole minutes; a meeting can run past midnight. ended_at is NULL while the meeting is still going.
Examples
Example 1
TeamsMeeting
| meeting_id | organizer | booked_minutes | started_at | ended_at |
|---|---|---|---|---|
| 1 | Priya | 30 | 2025-04-01 10:00:00 | 2025-04-01 10:42:00 |
| 2 | Rahul | 60 | 2025-04-01 11:00:00 | 2025-04-01 12:00:00 |
| 3 | Zara | 30 | 2025-04-01 14:00:00 | NULL |
| 4 | Arjun | 45 | 2025-04-02 09:05:00 | 2025-04-02 09:41:00 |
| 5 | Meera | 15 | 2025-04-02 16:00:00 | 2025-04-02 16:27:00 |
| 6 | Kabir | 60 | 2025-04-02 17:30:00 | 2025-04-02 18:45:00 |
| 7 | Diya | 30 | 2025-04-03 23:50:00 | 2025-04-04 00:25:00 |
Output
| meeting_id | organizer | overrun_minutes |
|---|---|---|
| 6 | Kabir | 15 |
| 1 | Priya | 12 |
| 5 | Meera | 12 |
| 7 | Diya | 5 |
How to solve Microsoft: Teams Meetings That Ran Past Their Booked Slot
The actual length of a meeting is TIMESTAMPDIFF(MINUTE, started_at, ended_at); the overrun is that minus booked_minutes. Because the difference is computed on full timestamps, a meeting from 23:50 to 00:25 is 35 minutes long, not negative — working with the clock time alone would break there.
The filter keeps overruns that are strictly positive: a meeting that ended exactly on time is not news. Meetings still running have ended_at NULL; the explicit IS NOT NULL documents that, and even without it the difference would be NULL and the comparison would drop the row.
The alternative computes the overrun once in a derived table and filters on overrun_minutes > 0, which relies on that NULL behaviour. Either way it is one scan; the final sort puts the worst offenders first and breaks ties by id so the order is fixed.
Reference solution (MySQL)
SELECT meeting_id, organizer,
TIMESTAMPDIFF(MINUTE, started_at, ended_at) - booked_minutes AS overrun_minutes
FROM TeamsMeeting
WHERE ended_at IS NOT NULL
AND TIMESTAMPDIFF(MINUTE, started_at, ended_at) > booked_minutes
ORDER BY overrun_minutes DESC, meeting_idAnother way
SELECT meeting_id, organizer, overrun_minutes FROM (
SELECT meeting_id, organizer, TIMESTAMPDIFF(MINUTE, started_at, ended_at) - booked_minutes AS overrun_minutes FROM TeamsMeeting
) t WHERE overrun_minutes > 0 ORDER BY overrun_minutes DESC, meeting_id ASC← Microsoft: Dev and Test VMs Still Billing Over the Weekend · Microsoft: Licensed Users With No Successful Sign-In →