Calculate valid daily usage with gap constraints
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write Standard SQL to compute, for a given date (use 2025-09-01), each user's total valid usage minutes. Schema and rules:
Schema (timestamps are UTC):
Table: user_activity
- user_id INT
- start_ts TIMESTAMP -- activity start
- end_ts TIMESTAMP -- activity end (end_ts > start_ts)
Rules:
1) Clip each activity to the inclusive day window [2025-09-01 00:00:00, 2025-09-01 23:59:59]. Activities fully outside this day contribute 0.
2) Merge overlapping or touching intervals after clipping (e.g., an interval ending at 09:00:00 and the next starting at 09:00:00 should merge).
3) Compute gaps between consecutive merged intervals for the same user within the day. If ANY gap (next.start - prev.end) exceeds 60 minutes, the user's entire day is invalid; return NULL for that user's usage on that date. Gaps of exactly 60 minutes are allowed.
4) For users without invalid gaps, return the sum of merged interval lengths (in whole minutes, floor) as usage_minutes.
5) Output: user_id, usage_minutes (NULL if invalid).
Sample data (minimal, for understanding):
user_activity
+---------+---------------------+---------------------+
| user_id | start_ts | end_ts |
+---------+---------------------+---------------------+
| 1 | 2025-09-01 00:30:00 | 2025-09-01 01:00:00 |
| 1 | 2025-09-01 01:10:00 | 2025-09-01 02:00:00 |
| 1 | 2025-09-01 03:05:00 | 2025-09-01 03:20:00 |
| 2 | 2025-08-31 23:50:00 | 2025-09-01 00:10:00 |
| 2 | 2025-09-01 00:20:00 | 2025-09-01 00:50:00 |
| 2 | 2025-09-01 02:00:00 | 2025-09-01 02:45:00 |
| 3 | 2025-09-01 10:00:00 | 2025-09-01 10:30:00 |
| 3 | 2025-09-01 11:35:00 | 2025-09-01 12:00:00 |
| 4 | 2025-09-01 23:30:00 | 2025-09-02 00:15:00 |
| 5 | 2025-09-01 08:00:00 | 2025-09-01 08:30:00 |
| 5 | 2025-09-01 08:35:00 | 2025-09-01 09:00:00 |
| 5 | 2025-09-01 10:00:00 | 2025-09-01 10:59:59 |
+---------+---------------------+---------------------+
Assumptions to honor: timestamps are precise to the second; ignore rows where end_ts <= start_ts; do not use procedural code—solve with SQL window functions/aggregation.
Overview: This question evaluates a candidate's ability to manipulate temporal intervals, including clipping to a specified day window, merging touching or overlapping segments, detecting gaps, and aggregating minute-level durations using SQL window functions and date/time arithmetic, and belongs to the Data Manipulation (SQL/Python) domain.