Employee Promotion Opportunities
Intuit● EasyFULLTIMEOAProblem statement
Return one row for every employee in employees. Join ManagerID to the manager's ID in the same table. Return columns Name, Salary, ManagerName, and PromotionOpportunity, in that order.
Display No Manager if ManagerID is null or no matching manager exists. Set PromotionOpportunity to Yes only when a matching manager exists and the employee's salary is strictly greater than that manager's salary; otherwise use No.
Sort by employee salary minus manager salary in descending order. Treat a missing manager salary as 0 for this sort. Break equal differences by employee ID in ascending order. Retain employees with equal or lower salaries and employees without managers.
Table schema
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.
employees
| Column | Type | Nullable | Description |
|---|---|---|---|
| IDPK | Integer | No | — |
| Name | Text | No | — |
| Salary | Integer | No | — |
| ManagerID | Integer | Yes | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| Name | Text | No | — |
| Salary | Integer | No | — |
| ManagerName | Text | No | — |
| PromotionOpportunity | Text | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- The employees table has 0 to 2000 rows with unique integer ID values between 1 and 1000000.
- Name is nonempty text with at most 100 characters; Salary is an integer between 0 and 1000000000.
- ManagerID is null or an integer between 1 and 1000000; it may have no matching employee.
- Names need not be unique. A manager relationship uses one direct lookup, including a self-reference or cycle.
- Use every employee exactly once, with the exact result labels and sort order stated above.