First and Last Contact Events | Dataford Interview Questions

How would you use SQL window functions to identify the first and last provider contact events for each user on RealSelf?

Use the provided users and provider contact event records. Include only users with at least one provider contact event, and use event_id as the tie-breaker when timestamps match.

Output

One row per qualifying user, ordered by user_id.
Include user_id, user_name, first and last contact event IDs, timestamps, and provider IDs.

Schema

users

Column	Type	Description
user_idPK	INT	Unique RealSelf user identifier
user_name	VARCHAR(100)	Display name of the user
created_at	TIMESTAMP	Timestamp when the user account was created

provider_contact_events

Column	Type	Description
event_idPK	INT	Unique contact event identifier
user_id	INT	User associated with the event
provider_id	INT	Provider associated with the event
event_type	VARCHAR(40)	Type of user event
contacted_at	TIMESTAMP	Timestamp when the event occurred

Your question is First and Last Contact Events. 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