Compute last-to-previous ad impression gaps

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

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).

Table impressions

column	type	constraints
ad_id	INT	NOT NULL
user_id	INT	NOT NULL
impression_ts	TIMESTAMP	NOT NULL

Sample data:

ad_id	impression_ts	user_id
101	2025-06-01 11:55:00	1
101	2025-06-01 12:00:00	1
101	2025-06-01 12:05:00	1
101	2025-06-01 13:00:00	2
101	2025-06-01 13:15:00	2
102	2025-06-01 14:00:00	1

Expected output

For each (user_id, ad_id) with at least two impressions, return the most recent and second most recent impression timestamps and the gap between them in seconds. With the sample data, (101,1) has impressions at 11:55, 12:00, 12:05, so last vs second last is 12:05 and 12:00 (300 seconds). (101,2) has impressions at 13:00 and 13:15, so the gap is 900 seconds. (102,1) is excluded because it has only one impression.

ad_id	diff_seconds	last_impression_ts	second_last_impression_ts	user_id
101	300	2025-06-01 12:05:00	2025-06-01 12:00:00	1
101	900	2025-06-01 13:15:00	2025-06-01 13:00:00	2

Hints

1. Rank impressions within each (ad_id, user_id) by impression_ts DESC.
2. Self-join the rows with rn = 1 and rn = 2 to compare the latest two impressions.
3. In PostgreSQL, use EXTRACT(EPOCH FROM (later_ts - earlier_ts)) for a seconds difference.