TikTok·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Sep 2025Remote

Summary

SQL window function question at TikTok for a Data Scientist role. Pretty focused technical screen, just the one problem but it had enough layers to keep you busy for a while.

Questions Asked (1)

Given an impressions table with ad_id, user_id, and a timestamp column, write a single SQL query that returns, for each (user_id, ad_id) pair with at least two impressions, the most recent timestamp, the second most recent timestamp, and the difference between them in seconds. Use ROW_NUMBER ordered by timestamp descending to break ties.

Data ModelingProduct Analytics & Metrics

Author's notes

The core mechanics clicked fast for me.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function (ROW_NUMBER) partitioned by user_id and ad_id, ordered by timestamp descending, to rank impressions. Then filter to the top two ranks per group and pivot using conditional aggregation (MAX(CASE WHEN rn=1 THEN timestamp END) etc.) to get the most recent and second most recent timestamps, and compute the difference in seconds. Ensure the query returns only groups with at least two impressions by filtering after ranking.

Pro tip: Explicitly mention that ROW_NUMBER is used instead of RANK or DENSE_RANK to guarantee unique sequential numbers even with ties, as specified. Also, note that the difference should be computed as the earlier timestamp minus the later timestamp (or vice versa) to get a positive value, and use TIMESTAMPDIFF or EXTRACT(EPOCH FROM ...) depending on the SQL dialect.

1. Rank impressions within each group

Use ROW_NUMBER() OVER (PARTITION BY user_id, ad_id ORDER BY timestamp DESC) to assign a rank to each impression, with the most recent getting rank 1.

2. Filter to top two ranks

In a subquery or CTE, select only rows where the rank is 1 or 2, ensuring each group has at least two impressions.

3. Pivot to get timestamps side by side

Use conditional aggregation (e.g., MAX(CASE WHEN rn=1 THEN timestamp END) AS most_recent, MAX(CASE WHEN rn=2 THEN timestamp END) AS second_most_recent) grouped by user_id and ad_id.

4. Compute time difference

Calculate the difference in seconds between the two timestamps, ensuring the result is positive (e.g., second_most_recent - most_recent or using TIMESTAMPDIFF).

5. Return final result

Select user_id, ad_id, most_recent, second_most_recent, and the computed difference, filtering out any groups that do not have both timestamps.

Key Points to Mention

Use of ROW_NUMBER() with PARTITION BY and ORDER BY to handle ties as specified.
Filtering to ranks 1 and 2 to ensure at least two impressions per group.
Conditional aggregation (CASE WHEN) to pivot the ranked rows into columns.
Computing the time difference in seconds, considering SQL dialect (e.g., TIMESTAMPDIFF, EXTRACT(EPOCH)).
Ensuring the final output includes only groups with at least two impressions.
Handling potential NULLs or edge cases where timestamps might be identical (though ROW_NUMBER breaks ties arbitrarily).

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