Employee swipe in / swipe out

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

swipe_events captures security turnstile badge swipes with columns employee_id, swipe_time, and swipe_type ('IN' or 'OUT'). For each employee and each date, calculate: minutes_inside: total minutes spent inside the office, computed by pairing each 'IN' swipe with its corresponding consecutive 'OUT' swipe. total_span_minutes: gross minutes between their very first swipe and their very last swipe of that day. Order by employee_id and swipe_date.

Requirements

• Output columns: employee_id, swipe_date, minutes_inside, total_span_minutes.
• Order by employee_id ascending, swipe_date ascending.

Constraints

• Swipes strictly alternate IN and OUT on the same day.
• Every day starts with IN and ends with OUT.
• Times are recorded as timestamps.

Examples

Input: swipe_events employee_id,swipe_time,swipe_type 101,2024-09-01 09:00:00,IN 101,2024-09-01 12:30:00,OUT 101,2024-09-01 13:30:00,IN 101,2024-09-01 17:30:00,OUT 102,2024-09-01 09:15:00,IN 102,2024-09-01 18:00:00,OUT Output: employee_id | swipe_date | minutes_inside | total_span_minutes 101 | 2024-09-01 | 450 | 510 102 | 2024-09-01 | 525 | 525 Why this passes: Employee 101 was inside 210 + 240 = 450 minutes across two sessions but spanned 510 minutes from first IN to last OUT (the lunch gap is excluded from the first number); 102 had one session so both numbers match.

Input: swipe_events employee_id | swipe_time | swipe_type 5 | 2024-09-01 10:00:00 | IN 5 | 2024-09-01 14:00:00 | OUT Output: employee_id | swipe_date | minutes_inside | total_span_minutes 5 | 2024-09-01 | 240 | 240 Why this passes: One continuous 4-hour session yields 240 minutes for both columns.

Topics: lakebench, sql, lead, event-pairing.

More SQL interview questions · All interview problems · Learn data engineering