Compute last-to-previous ad impression gaps

Q: Compute last-to-previous ad impression gaps

This is a Data Manipulation (SQL/Python) interview question from TikTok for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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.

Compute last-to-previous ad impression gaps

Comments (0)