SQL First Message Per User | Dataford Interview Questions

Your question is SQL First Message Per User. Start with the requirements and the one table 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

Tinder's messaging analytics team wants to identify the first message sent by each user. This helps measure how quickly new matches begin conversations.

Write a PostgreSQL query against user_messages to return the first message for every user with at least one non-null timestamp.

Requirements

Use a window function to rank messages separately for each user_id.
Select the earliest non-null timestamp for each user.
If a user has multiple messages at the same timestamp, choose the lexicographically smallest message_id as the deterministic tie-breaker.
Return user_id, message_id, and timestamp, ordered by user_id.

Schema

user_messages

Column	Type	Description
user_id	INTEGER	Tinder user who sent the message
message_idPK	VARCHAR(12)	Unique identifier for the message
timestamp	TIMESTAMPTZ	Timestamp when the message was sent