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.
Requirements
- Consider only active employees and login events with a non-null
login_at.
- Return exactly one row, even when multiple employees have the same latest timestamp.
- Break timestamp ties deterministically using the greatest
event_id.
- Include the employee ID, employee name, login timestamp, department name, and site name.
- Use a window function and structure the query so an index on
(login_at DESC, event_id DESC) can support the large event table.
