First and Last User Interactions

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is First and Last User Interactions. Start with the requirements and the two 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.

You need to log in / sign up to run or submit.

Problem

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

Tablesusersevent_logs

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results