Microsoft: Teams Attendees Present for at Least 75% of a Meeting

Hard
791.

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 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

ColumnType
meeting_idint
titlevarchar
start_atdatetime
end_atdatetime

Primary key: meeting_id.

One row per scheduled meeting; times are whole minutes.

Table: AttendanceSession

ColumnTypeValues
session_idint
meeting_idint
attendeevarchar
deviceenumdesktop, mobile, web
joined_atdatetime
left_atdatetime

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

TeamsMeeting

meeting_idtitlestart_atend_at
1Sprint review2025-05-06 10:00:002025-05-06 11:00:00
2Design sync2025-05-07 15:00:002025-05-07 15:40:00

AttendanceSession

session_idmeeting_idattendeedevicejoined_atleft_at
11Priyadesktop2025-05-06 09:58:002025-05-06 10:30:00
21Priyamobile2025-05-06 10:20:002025-05-06 10:50:00
31Rahuldesktop2025-05-06 10:05:002025-05-06 10:25:00
41Rahulweb2025-05-06 10:30:002025-05-06 10:55:00
51Zaradesktop2025-05-06 10:00:002025-05-06 10:44:00
61Arjunmobile2025-05-06 10:50:002025-05-06 11:30:00
72Priyaweb2025-05-07 15:00:002025-05-07 15:20:00
82Priyadesktop2025-05-07 15:05:002025-05-07 15:15:00
92Priyadesktop2025-05-07 15:25:002025-05-07 15:40:00
102Kabirdesktop2025-05-07 14:50:002025-05-07 14:59:00
1 more row

Output

meeting_idattendeepresent_minutes
1Priya50
1Rahul45
2Priya35
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
meeting_idint
titlevarchar
start_atdatetime
end_atdatetime

Primary key: meeting_id.

One row per scheduled meeting; times are whole minutes.

Table: AttendanceSession

ColumnType
session_idint
meeting_idint
attendeevarchar
deviceenum(desktop, mobile, web)
joined_atdatetime
left_atdatetime

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_idtitlestart_atend_at
1Sprint review2025-05-06 10:00:002025-05-06 11:00:00
2Design sync2025-05-07 15:00:002025-05-07 15:40:00

AttendanceSession

session_idmeeting_idattendeedevicejoined_atleft_at
11Priyadesktop2025-05-06 09:58:002025-05-06 10:30:00
21Priyamobile2025-05-06 10:20:002025-05-06 10:50:00
31Rahuldesktop2025-05-06 10:05:002025-05-06 10:25:00
41Rahulweb2025-05-06 10:30:002025-05-06 10:55:00
51Zaradesktop2025-05-06 10:00:002025-05-06 10:44:00
61Arjunmobile2025-05-06 10:50:002025-05-06 11:30:00
72Priyaweb2025-05-07 15:00:002025-05-07 15:20:00
82Priyadesktop2025-05-07 15:05:002025-05-07 15:15:00
92Priyadesktop2025-05-07 15:25:002025-05-07 15:40:00
102Kabirdesktop2025-05-07 14:50:002025-05-07 14:59:00
112Kabirdesktop2025-05-07 15:10:002025-05-07 15:39:00

Output

meeting_idattendeepresent_minutes
1Priya50
1Rahul45
2Priya35

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) * 3

Another 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 →