Estimate Page-Visit Duration from the Next User Event
Company: Airbnb
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Estimate the time spent on each page visit from the next recorded page event for the same user.
### Input Table
`page_events`
| Column | Type | Meaning |
| --- | --- | --- |
| `event_id` | INTEGER | Unique event identifier; primary key |
| `user_id` | INTEGER | Non-null user identifier |
| `page_id` | TEXT | Non-null page identifier |
| `event_time` | TIMESTAMP | Non-null event timestamp with whole-second precision |
### Output Contract
Write one read-only PostgreSQL query returning `event_id`, `user_id`, `page_id`, and `duration_seconds` for every input event, ordered by `event_id` ascending.
For this exercise, a user's next page event ends the current page visit. Define the next event by ascending `(event_time, event_id)` within that user. `duration_seconds` is the elapsed number of seconds from the current event to that next event. If there is no next event for the user, return SQL `NULL` for the duration.
### Example
| event_id | user_id | page_id | event_time |
| --- | --- | --- | --- |
| 1 | 10 | home | 2026-01-01 10:00:00 |
| 2 | 10 | search | 2026-01-01 10:00:12 |
| 3 | 20 | home | 2026-01-01 10:00:04 |
| 4 | 10 | detail | 2026-01-01 10:00:12 |
Expected result:
| event_id | user_id | page_id | duration_seconds |
| --- | --- | --- | --- |
| 1 | 10 | home | 12 |
| 2 | 10 | search | 0 |
| 3 | 20 | home | NULL |
| 4 | 10 | detail | NULL |
### Constraints and Clarifications
These are explicit measurement assumptions: there are no session or tab identifiers, no inactivity cutoff, and no additional page-exit events. Use the next event for the same user even across a long gap. The result is a next-event estimate, not proof of uninterrupted attention to a page.
- Two events for one user may have equal timestamps; the smaller `event_id` comes first.
- A repeated visit to the same page still creates a separate row.
- Events for different users never end one another's visits.
- Retain each user's final event with a null duration rather than assuming a zero-length visit.
```hint Look at the neighboring event within a user
The duration belongs to the current row, but its ending timestamp comes from a later row. Establish the partition and ordering before subtracting timestamps.
```
Overview: Estimate page-visit duration with each user’s next event, deterministic timestamp ties, and null durations for final events.
Read the full Airbnb Data Engineer interview experience this question came from
Estimate the time spent on each page visit from the next recorded page event for the same user.
Table `page_events` has columns `event_id` (INTEGER, unique event identifier, primary key), `user_id` (INTEGER, non-null user identifier), `page_id` (TEXT, non-null page identifier), and `event_time` (TIMESTAMP, non-null event timestamp with whole-second precision).
Write one read-only PostgreSQL query returning `event_id`, `user_id`, `page_id`, and `duration_seconds` for every input event, ordered by `event_id` ascending.
A user's next page event ends the current page visit. Define the next event by ascending `(event_time, event_id)` within that user. `duration_seconds` is the elapsed number of seconds from the current event to that next event. If there is no next event for the user, return SQL NULL for the duration.
These are explicit measurement assumptions: there are no session or tab identifiers, no inactivity cutoff, and no additional page-exit events. Use the next event for the same user even across a long gap. The result is a next-event estimate, not proof of uninterrupted attention to a page.
- Two events for one user may have equal timestamps; the smaller `event_id` comes first.
- A repeated visit to the same page still creates a separate row.
- Events for different users never end one another's visits.
- Retain each user's final event with a null duration rather than assuming a zero-length visit.
Tables
page_events(event_id INTEGER, user_id INTEGER, page_id TEXT, event_time TIMESTAMP)
Hints
- The duration belongs to the current row, but its ending timestamp comes from a later row. Establish the partition and ordering before subtracting timestamps.