Your question is SQL Query for Latest User. Start with the requirements and the four 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.
Infineon Technologies Americas needs an operational query that identifies the employee whose login was recorded most recently. The production employee_login_events table contains more than 100 million rows, so the query must be deterministic and suitable for an indexed PostgreSQL workload.
Write a PostgreSQL query that returns exactly one row for the most recently logged-in active employee, along with department and site information.
login_at.event_id.(login_at DESC, event_id DESC) can support the large event table.| Column | Type | Description |
|---|---|---|
| employee_idPK | BIGINT | Unique employee identifier |
| employee_name | VARCHAR(120) | Employee full name |
| department_id | INT | Assigned department |
| site_id | INT | Assigned Infineon site |
| is_active | BOOLEAN | Whether the employee is active |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Unique login event identifier |
| employee_id | BIGINT | Employee who logged in |
| login_at | TIMESTAMPTZ | Recorded login timestamp |
| Column | Type | Description |
|---|---|---|
| department_idPK | INT | Department identifier |
| department_name | VARCHAR(100) | Department name |
| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Site identifier |
| site_name | VARCHAR(120) | Infineon site name |