Calculate valid daily usage with gap constraints
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.
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:
- 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.
- 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).
- 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.
- For users without invalid gaps, return the sum of merged interval lengths (in whole minutes, floor) as usage_minutes.
- 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.