Estimate Page-Visit Duration from the Next User Event

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.

Page Visit Duration from the Next Page Event

Table page_events

column	type	constraints
event_id	INTEGER	PRIMARY KEY
user_id	INTEGER	NOT NULL
page_id	TEXT	NOT NULL
event_time	TIMESTAMP	NOT NULL

Sample data:

event_id	event_time	page_id	user_id
1	2026-03-02 09:00:00	home	101
2	2026-03-02 09:00:05	home	202
3	2026-03-02 09:03:00	checkout	202
4	2026-03-02 09:00:45	search	101
5	2026-03-02 09:01:00	pricing	303
6	2026-03-02 09:02:10	product_42	101
8	2026-03-02 09:01:30	product_17	202
10	2026-03-02 09:02:40	search	101
11	2026-03-02 09:05:00	product_42	101
12	2026-03-02 09:01:30	cart	202
13	2026-03-02 14:05:00	home	101
14	2026-03-02 23:59:50	docs	404
15	2026-03-03 00:00:20	docs	404

Expected output

event_id	user_id	page_id	duration_seconds
1	101	home	45
2	202	home	85
3	202	checkout	NULL
4	101	search	85
5	303	pricing	NULL
6	101	product_42	30
8	202	product_17	0
10	101	search	140
11	101	product_42	18000
12	202	cart	90
13	101	home	NULL
14	404	docs	30
15	404	docs	NULL

Hints

1. The duration belongs to the current row, but its ending timestamp comes from a later row. Establish the partition and ordering before subtracting timestamps.