Katkat
Back

Compute last-to-previous ad impression gaps

TikTokData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.