SQL: First and Last Event Per User | Dataford Interview Questions

Business Context

NewWave Telecom & Technologies uses customer activity events to analyze engagement across its digital services. The Data Analyst team needs a per-user lifecycle summary for the NewWave customer activity dashboard.

Task

Write a PostgreSQL query that returns the first and last valid event for every user in users, including users who have no valid events.

Requirements

Exclude events whose event_at is NULL.
Rank events independently for each user by timestamp, using event_id as the deterministic tie-breaker.
Return the first and last event timestamps and event names.
Return elapsed minutes between the first and last event, or NULL when no valid event exists.
Preserve users without events, and label unmatched event types as Unknown.
Sort by user_id.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
full_name	VARCHAR(100)	User's full name
account_status	VARCHAR(20)	Current account status
created_at	TIMESTAMP	Account creation timestamp

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_type_id	INT	Referenced event type
event_at	TIMESTAMP	Timestamp when the event occurred

event_types

Column	Type	Description
event_type_idPK	INT	Unique event type identifier
event_name	VARCHAR(50)	Readable event type name

Your question is SQL: First and Last Event Per User. Start with the requirements and the three 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