TikTok·Data Scientist·Technical Phone Screen·Senior

SeniorPrefer not to say

Jul 2026Remote

Summary

TikTok Data Scientist SQL round, one question but it was a beast. The kind of problem where you think you understand it after the first read and then realize you missed three edge cases by the second.

Questions Asked (1)

Write standard SQL (no procedural code) to compute each user's total valid usage minutes for a specific date. You need to clip activity intervals to that day's boundaries, merge overlapping or adjacent intervals per user, then check whether any gap between consecutive merged intervals exceeds 60 minutes. If any such gap exists, return NULL for that user's usage. Otherwise return the total merged duration in whole minutes (floor).

Data ModelingAlgorithms & Data StructuresProduct Analytics & Metrics

Author's notes

The clipping part I got pretty quickly, GREATEST and LEAST to bound start and end to the day window.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into stages using CTEs: first clip intervals to the target date, then merge overlapping/adjacent intervals per user, then compute gaps between merged intervals to check if any exceeds 60 minutes, and finally return either the total duration or NULL. Use window functions like LAG and cumulative sums to identify merge groups and gaps without procedural code.

Pro tip: Explicitly state your assumptions about interval boundaries (inclusive/exclusive) and how you handle edge cases like intervals exactly 60 minutes apart or touching intervals, as these details often determine correctness in production.

1. Clip intervals to the target date

Filter and adjust activity intervals so they fall within the specified day, using GREATEST and LEAST to clip start and end times to the day's boundaries.

2. Merge overlapping or adjacent intervals per user

Use a gaps-and-islands technique: order intervals by start time, flag new groups when the start exceeds the running maximum end, then aggregate to get merged intervals.

3. Check for gaps exceeding 60 minutes

For each user, compute the difference between the start of each merged interval and the end of the previous one; if any gap > 60 minutes, mark the user for NULL output.

4. Compute total duration or return NULL

Sum the durations of merged intervals for users without large gaps, floor to whole minutes, and return NULL for users with any gap > 60 minutes.

Key Points to Mention

Use of CTEs to structure the query logically and avoid nested subqueries.
Window functions (LAG, SUM OVER) to identify merge groups and gaps.
Handling of interval boundaries: clipping with GREATEST/LEAST and inclusive/exclusive end times.
Definition of 'adjacent' intervals (e.g., end == next start) and how they are merged.
Threshold logic: gap > 60 minutes triggers NULL, not >=.
Flooring total duration to whole minutes using FLOOR or integer division.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.