Most Recent Record Per User | Dataford Interview Questions

Apex Systems needs a clean login audit view from its internal authentication records. The source table may contain multiple login events for the same user, including events with tied or missing timestamps.

Write a PostgreSQL query that returns only the most recent login for each unique user.

Requirements

Exclude records where user_id is NULL.
Use ROW_NUMBER() to rank logins within each user by login_at descending.
Break timestamp ties with the larger login_id.
Return the selected login details, ordered by user_id.

Schema

apex_login_records

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	Identifier of the user who attempted to log in
login_at	TIMESTAMPTZ	Time of the login event
login_status	VARCHAR(20)	Outcome of the login attempt
ip_address	VARCHAR(45)	IP address associated with the login attempt

Your question is Most Recent Record Per User. Start with the requirements and the one table 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

Write a PostgreSQL query that returns only the most recent login for each unique user.

Requirements

Exclude records where user_id is NULL.
Use ROW_NUMBER() to rank logins within each user by login_at descending.
Break timestamp ties with the larger login_id.
Return the selected login details, ordered by user_id.

Schema

apex_login_records

Column	Type	Description
login_idPK	INT	Unique login event identifier
user_id	INT	Identifier of the user who attempted to log in
login_at	TIMESTAMPTZ	Time of the login event
login_status	VARCHAR(20)	Outcome of the login attempt
ip_address	VARCHAR(45)	IP address associated with the login attempt

Your question is Most Recent Record Per User. Start with the requirements and the one table 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.