Your question is Most Recent Record 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.
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.
user_id is NULL.ROW_NUMBER() to rank logins within each user by login_at descending.login_id.user_id.| 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 |