Employees with the Highest and Lowest Salary
Mastercard● MediumFULLTIMEPHONE SCREENProblem statement
Return every employee whose salary equals the company-wide maximum or minimum.
Join each worker to the supplied current title and add salary_type: Highest Salary for the maximum and Lowest Salary for the minimum.
Table schema
MySQLPostgreSQLPandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
workers
| Column | Type | Nullable | Description |
|---|---|---|---|
| worker_idPK | Integer | No | — |
| first_name | Text | No | — |
| last_name | Text | No | — |
| salary | Decimal | No | — |
| joining_date | Date | No | — |
| department | Text | No | — |
titles
| Column | Type | Nullable | Description |
|---|---|---|---|
| worker_ref_idPK | Integer | No | — |
| worker_title | Text | No | — |
| affected_from | Date | No | — |
Foreign key: worker_ref_id → workers(worker_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| worker_id | Integer | No | — |
| first_name | Text | No | — |
| last_name | Text | No | — |
| salary | Decimal | No | — |
| salary_type | Text | No | — |
| worker_title | Text | No | — |
| department | Text | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- The data contains at least two distinct salary values.
- Each worker has exactly one row in
titles. - Return all ties, highest rows first, and worker IDs ascending within each group.