Compute last-to-previous ad impression gaps
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Given impressions(ad_id INT, user_id INT, impression_ts TIMESTAMP) with multiple impressions per (user_id, ad_id), write a single SQL query to return, for each (user_id, ad_id) that has at least two impressions, the last impression timestamp, the second‑last impression timestamp, and the time difference between them in seconds as diff_seconds. Break ties by timestamp, using ROW_NUMBER within each (user_id, ad_id) ordered by impression_ts DESC. Assume UTC timestamps. Provide the query in standard SQL using window functions. Use the sample data below (today is 2025‑09‑01):
Schema:
- impressions(ad_id INT, user_id INT, impression_ts TIMESTAMP)
Sample rows:
+--------+---------+---------------------+
| ad_id | user_id | impression_ts |
+--------+---------+---------------------+
| 101 | 1 | 2025-09-01 11:55:00 |
| 101 | 1 | 2025-09-01 12:00:00 |
| 101 | 1 | 2025-09-01 12:05:00 |
| 101 | 2 | 2025-09-01 13:00:00 |
| 101 | 2 | 2025-09-01 13:15:00 |
| 102 | 1 | 2025-09-01 14:00:00 |
+--------+---------+---------------------+
Expected outputs include (101,1) diff_seconds=300 and (101,2) diff_seconds=900; exclude (102,1) because it has only one impression.
Quick Answer: This question evaluates proficiency in data manipulation and time-based analytics, covering SQL window functions, timestamp arithmetic, and grouping/ordering semantics within the Data Manipulation (SQL/Python) domain.