Microsoft: Teams Attendees Present for at Least 75% of a Meeting
A Teams attendance report feeds a training team's completion records: an attendee completes a session when they were present for at least three quarters of…
- Difficulty: Hard
- Topics: Window Functions, Dates, Aggregation
- Dialect: MySQL
- Problem: #791
Problem statement
A Teams attendance report feeds a training team's completion records: an attendee completes a session when they were present for at least three quarters of the scheduled meeting.
People join from several devices at once and drop and rejoin, so their join sessions overlap. Count an attendee's present minutes as the minutes of the scheduled window (start_at to end_at) covered by at least one of their sessions — time in the lobby before the start or after the end does not count, and overlapping sessions count once. Return meeting_id, attendee and present_minutes for every attendee whose present minutes × 4 ≥ the meeting's scheduled minutes × 3. Return the rows in any order.
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 |
| title | varchar |
| start_at | datetime |
| end_at | datetime |
Primary key: meeting_id.
One row per scheduled meeting; times are whole minutes.
Table: AttendanceSession
| Column | Type |
|---|---|
| session_id | int |
| meeting_id | int |
| attendee | varchar |
| device | enum(desktop, mobile, web) |
| joined_at | datetime |
| left_at | datetime |
Primary key: session_id.
One row per join from one device; left_at is after joined_at. Times are whole minutes and may fall outside the scheduled window.
Examples
Example 1
TeamsMeeting
| meeting_id | title | start_at | end_at |
|---|---|---|---|
| 1 | Sprint review | 2025-05-06 10:00:00 | 2025-05-06 11:00:00 |
| 2 | Design sync | 2025-05-07 15:00:00 | 2025-05-07 15:40:00 |
AttendanceSession
| session_id | meeting_id | attendee | device | joined_at | left_at |
|---|---|---|---|---|---|
| 1 | 1 | Priya | desktop | 2025-05-06 09:58:00 | 2025-05-06 10:30:00 |
| 2 | 1 | Priya | mobile | 2025-05-06 10:20:00 | 2025-05-06 10:50:00 |
| 3 | 1 | Rahul | desktop | 2025-05-06 10:05:00 | 2025-05-06 10:25:00 |
| 4 | 1 | Rahul | web | 2025-05-06 10:30:00 | 2025-05-06 10:55:00 |
| 5 | 1 | Zara | desktop | 2025-05-06 10:00:00 | 2025-05-06 10:44:00 |
| 6 | 1 | Arjun | mobile | 2025-05-06 10:50:00 | 2025-05-06 11:30:00 |
| 7 | 2 | Priya | web | 2025-05-07 15:00:00 | 2025-05-07 15:20:00 |
| 8 | 2 | Priya | desktop | 2025-05-07 15:05:00 | 2025-05-07 15:15:00 |
| 9 | 2 | Priya | desktop | 2025-05-07 15:25:00 | 2025-05-07 15:40:00 |
| 10 | 2 | Kabir | desktop | 2025-05-07 14:50:00 | 2025-05-07 14:59:00 |
| 11 | 2 | Kabir | desktop | 2025-05-07 15:10:00 | 2025-05-07 15:39:00 |
Output
| meeting_id | attendee | present_minutes |
|---|---|---|
| 1 | Priya | 50 |
| 1 | Rahul | 45 |
| 2 | Priya | 35 |
How to solve Microsoft: Teams Attendees Present for at Least 75% of a Meeting
Three steps: clip, merge, measure. Clip each session to the scheduled window — GREATEST(joined_at, start_at) to LEAST(left_at, end_at) — and drop sessions that do not overlap it at all (Kabir's 14:50–14:59 lobby wait). Lobby and overtime minutes vanish here.
Merge is the classic interval-union pattern. Order an attendee's clipped sessions by start; MAX(seg_end) over all previous rows is how far they were already covered. A session that starts after that point opens a new block; a running SUM of the "new block" flags numbers the blocks, and each block spans MIN(start) to MAX(end). Priya's desktop 10:00–10:30 and phone 10:20–10:50 become one 50-minute block, not 60 minutes; a session wholly inside another adds nothing because the running max already covers it.
Measure: sum block lengths and compare present × 4 ≥ scheduled × 3, integer arithmetic so exactly 75% (Rahul's 45 of 60) passes. The alternative counts covered minutes on a generated minute grid with a recursive CTE — simpler to trust, but its cost grows with meeting length × attendees, while the window version is a sort per attendee.
Reference solution (MySQL)
WITH clipped AS (
SELECT s.meeting_id, s.attendee, s.session_id,
GREATEST(s.joined_at, m.start_at) AS seg_start,
LEAST(s.left_at, m.end_at) AS seg_end
FROM AttendanceSession s
JOIN TeamsMeeting m ON m.meeting_id = s.meeting_id
WHERE s.joined_at < m.end_at AND s.left_at > m.start_at
), reach AS (
SELECT c.*, MAX(seg_end) OVER (PARTITION BY meeting_id, attendee ORDER BY seg_start, session_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS covered_until
FROM clipped c
), islands AS (
SELECT r.*, SUM(CASE WHEN covered_until IS NULL OR seg_start > covered_until THEN 1 ELSE 0 END)
OVER (PARTITION BY meeting_id, attendee ORDER BY seg_start, session_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS island
FROM reach r
), merged AS (
SELECT meeting_id, attendee, island, MIN(seg_start) AS a, MAX(seg_end) AS b
FROM islands GROUP BY meeting_id, attendee, island
), present AS (
SELECT meeting_id, attendee, SUM(TIMESTAMPDIFF(MINUTE, a, b)) AS present_minutes
FROM merged GROUP BY meeting_id, attendee
)
SELECT p.meeting_id, p.attendee, p.present_minutes
FROM present p
JOIN TeamsMeeting m ON m.meeting_id = p.meeting_id
WHERE p.present_minutes * 4 >= TIMESTAMPDIFF(MINUTE, m.start_at, m.end_at) * 3Another way
WITH RECURSIVE minute_no AS (
SELECT 0 AS k UNION ALL SELECT k + 1 FROM minute_no WHERE k < 239
), grid AS (
SELECT m.meeting_id, DATE_ADD(m.start_at, INTERVAL n.k MINUTE) AS minute_at
FROM TeamsMeeting m JOIN minute_no n ON n.k < TIMESTAMPDIFF(MINUTE, m.start_at, m.end_at)
), people AS (
SELECT DISTINCT meeting_id, attendee FROM AttendanceSession
)
SELECT p.meeting_id, p.attendee, COUNT(*) AS present_minutes
FROM people p
JOIN grid g ON g.meeting_id = p.meeting_id
WHERE EXISTS (SELECT 1 FROM AttendanceSession s
WHERE s.meeting_id = p.meeting_id AND s.attendee = p.attendee
AND s.joined_at <= g.minute_at AND s.left_at > g.minute_at)
GROUP BY p.meeting_id, p.attendee
HAVING COUNT(*) * 4 >= (SELECT TIMESTAMPDIFF(MINUTE, m2.start_at, m2.end_at) FROM TeamsMeeting m2 WHERE m2.meeting_id = p.meeting_id) * 3← Microsoft: Privileged Azure Role Holders for the Access Review · Microsoft: OneDrive Files Whose Full Path Is Too Long to Sync →