First and Last User Events | Dataford Interview Questions

Your question is First and Last User Events. Start with the requirements and the three tables on the right.

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

Problem

StreamWave tracks user activity across web and mobile sessions. Write a PostgreSQL query to return each active user's first and last event based on event timestamp.

Requirements

Include only users whose status is 'active'.
Return one row per active user, even if the user has no events.
For each user, show the first event type and timestamp, and the last event type and timestamp.
If multiple events share the same timestamp for a user, break ties using the smaller event_id for the first event and the larger event_id for the last event.
Order the final result by user_id.

Schema

users

Column	Type	Description
user_idPK	INT	Primary key for the user
full_name	VARCHAR(100)	User full name
status	VARCHAR(20)	Account status such as active, inactive, or banned
signup_date	DATE	Date the user signed up

platforms

Column	Type	Description
platform_idPK	INT	Primary key for the platform
platform_name	VARCHAR(50)	Platform name such as web or ios

events

Column	Type	Description
event_idPK	INT	Primary key for the event
user_id	INT	User who generated the event
platform_id	INT	Platform where the event occurred
event_type	VARCHAR(50)	Type of event
event_time	TIMESTAMP	Timestamp when the event occurred