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.
Overview: 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.
You are given a table impressions(ad_id INT, user_id INT, impression_ts TIMESTAMP) that stores ad impression events. Each (user_id, ad_id) pair can have multiple impressions over time.
Write a single SQL query that returns, for every (user_id, ad_id) that has at least two impressions, the following columns:
- last_impression_ts: the most recent impression timestamp
- second_last_impression_ts: the second most recent impression timestamp
- diff_seconds: the time difference between these two timestamps in seconds
Break ties by timestamp: within each (user_id, ad_id), order impressions by impression_ts DESC and use ROW_NUMBER to identify the most recent (rn = 1) and second most recent (rn = 2) impressions. Assume all timestamps are in UTC.
Use PostgreSQL with window functions, and render the two timestamp columns as `YYYY-MM-DD HH24:MI:SS`. Use the schema and sample data below; the sample timestamps are on 2025-06-01 (UTC).
Tables
impressions(ad_id INT, user_id INT, impression_ts TIMESTAMP)
Hints
- Rank impressions within each `(ad_id, user_id)` by `impression_ts DESC`.
- Self-join the rows with `rn = 1` and `rn = 2` to compare the latest two impressions.