Latest Record Per User | Dataford Interview Questions

Write a SQL query to join two tables and return the latest record per user.

Return every user, including users without a record. Treat the greatest recorded_at value as latest, and use the greatest record_id to break timestamp ties.

Output

One row per user, ordered by user_id ascending
Columns: user_id, user_name, record_id, recorded_at, and status
Users without records should have NULL values for the record columns

Schema

users

Column	Type	Description
user_idPK	INT	Unique identifier for the user
user_name	VARCHAR(100)	Display name of the user
email	VARCHAR(255)	User email address

user_records

Column	Type	Description
record_idPK	INT	Unique identifier for the record
user_id	INT	User associated with the record
recorded_at	TIMESTAMP	Timestamp when the record was created
status	VARCHAR(30)	Status stored in the record

Your question is Latest Record Per User. 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