SQL Windows and Lag/Lead | Dataford Interview Questions

How would you write SQL queries using lag/lead and window functions to compute time-based features?

Use the provided users and activity_events tables. Return event-level features showing each event's previous and next event timestamps and the elapsed seconds between adjacent events.

Output

One row per activity event, including unmatched user IDs.
Columns: event_id, user_id, user_name, event_type, event_at, previous_event_at, next_event_at, seconds_since_previous, and seconds_until_next.
Order by user_id with NULL user IDs last, then event_at and event_id.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	Display name of the user

activity_events

Column	Type	Description
event_idPK	INT	Unique activity event identifier
user_id	INT	User associated with the event
event_type	VARCHAR(40)	Type of activity event
event_at	TIMESTAMP	Timestamp when the event occurred

Your question is SQL Windows and Lag/Lead. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem