For each event, return the next event's timestamp for the same user and the seconds between them. The last event per user gets NULL for both.
Use strftime(…, '%Y-%m-%d %H:%M:%S') to stringify both timestamps for grading stability.
Columns: user_id, event_ts (the event's own timestamp, formatted), next_ts (the next event's timestamp, formatted), seconds_to_next. Sort by user_id then event_ts ascending.
About this problem
This problem practises LEAD, the forward-looking twin of LAG, and turning the gap between two timestamps into a number. For each row in events, you find the same user’s next event, report when it happened, and measure how many seconds passed in between – a building block for dwell time and time-to-next-action metrics. The window must be defined per user_id and ordered by event_ts, and each user’s last event has no successor, so both next_ts and seconds_to_next are NULL. Both timestamps are returned as strings formatted with strftime.
The quiet failure is again a missing PARTITION BY user_id. Without it, the “next” event is simply the next row in the table’s timeline, which often belongs to a different user. Some users in the sample have a single event, so a global LEAD gives them someone else’s timestamp and a positive number of seconds instead of NULL, and the output still looks entirely reasonable. The second trap is order of operations: if you format the timestamps with strftime first and then try to subtract, you are working with text, not time. Check, too, that a gap of more than a day comes out as the full number of seconds: reading only the seconds component of the interval gives a small, believable number that is wrong.
Read the lesson: LAG, LEAD & Value Functions (P17) →