First and Last User Interactions | Dataford Interview Questions

Precisely wants to analyze user activity across its data quality and location intelligence products. Write a PostgreSQL query that reports the first and last valid interaction for every user, including users who have no valid event records.

A valid interaction must have a non-null event_type. If multiple events have the same timestamp, use the lowest event_id as the deterministic tie-breaker.

Requirements

Return every user from users, including users without valid interactions.
Return the timestamp and event type for each user's first and last valid interaction.
Use a window function to rank events chronologically within each user.
Sort the final result by user_id ascending.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
display_name	VARCHAR(100)	User's display name

event_logs

Column	Type	Description
event_idPK	INTEGER	Unique event identifier
user_id	INTEGER	User associated with the event
occurred_at	TIMESTAMPTZ	Timestamp when the interaction occurred
event_type	VARCHAR(50)	Type of interaction
source	VARCHAR(50)	Application or channel source

Your question is First and Last User Interactions. 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