Department Runner-Up Salary Members

Medium

A compensation analytics service stores one row per employee in a table named employees. Table: employees emp_id: unique employee identifier salary: employee salary dept: department label Your task is to identify employees who sit in the second salary tier of their department. Definition of second salary tier: Within each department, take DISTINCT salary values. Sort those values in descending order. Pick the second value in that ordering. Return every employee in that department whose salary equals that value. Important behavior: If multiple employees share the second-highest salary, include all of them. If a department has fewer than two distinct salary values, it contributes no rows. Input row order is arbitrary and must not affect the result. Return exactly these columns in this order: emp_id dept Final ordering: emp_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

employees:
| emp_id | salary | dept     |
|--------|--------|----------|
| 11     | 120000 | Platform |
| 12     | 135000 | Platform |
| 13     | 135000 | Platform |
| 14     | 150000 | Platform |
| 21     | 90000  | Risk     |
| 22     | 90000  | Risk     |
| 23     | 87000  | Risk     |
| 31     | 76000  | Growth   |
| 32     | 81000  | Growth   |
| 41     | 50000  | Legal    |

Output

[
  {"emp_id":12,"dept":"Platform"},
  {"emp_id":13,"dept":"Platform"},
  {"emp_id":23,"dept":"Risk"},
  {"emp_id":31,"dept":"Growth"}
]

Explanation

Platform runner-up salary is 135000 and includes two employees. Risk runner-up salary is 87000. Growth runner-up salary is 76000. Legal has one distinct salary and is excluded.

Example 2

Input

employees:
| emp_id | salary | dept  |
|--------|--------|-------|
| 1      | 70000  | Ops   |
| 2      | 70000  | Ops   |
| 3      | 55000  | Audit |
| 4      | 55000  | Audit |

Output

[]

Explanation

Each department has only one distinct salary level, so no second-highest value exists.

Example 3

Input

employees:
| emp_id | salary | dept      |
|--------|--------|-----------|
| 101    | 200000 | Security  |
| 102    | 190000 | Security  |
| 103    | 190000 | Security  |
| 104    | 170000 | Security  |
| 201    | 95000  | Support   |
| 202    | 92000  | Support   |
| 203    | 92000  | Support   |
| 204    | 91000  | Support   |

Output

[
  {"emp_id":102,"dept":"Security"},
  {"emp_id":103,"dept":"Security"},
  {"emp_id":202,"dept":"Support"},
  {"emp_id":203,"dept":"Support"}
]

Explanation

In both departments, the second-highest distinct salary appears multiple times, so all tied employees are returned.

Constraints

1 <= number of rows in employees <= 200000
emp_id is unique within the table
-1000000000 <= salary <= 1000000000
dept is a non-empty string
Result must include all employees tied at each department's second-highest distinct salary
Departments with fewer than two distinct salary values produce no result rows
Return output columns exactly in the order: emp_id, dept
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

employees: | emp_id | salary | dept | |--------|--------|----------| | 11 | 120000 | Platform | | 12 | 135000 | Platform | | 13 | 135000 | Platform | | 14 | 150000 | Platform | | 21 | 90000 | Risk | | 22 | 90000 | Risk | | 23 | 87000 | Risk | | 31 | 76000 | Growth | | 32 | 81000 | Growth | | 41 | 50000 | Legal |

employees: | emp_id | salary | dept | |--------|--------|-----------| | 101 | 200000 | Security | | 102 | 190000 | Security | | 103 | 190000 | Security | | 104 | 170000 | Security | | 201 | 95000 | Support | | 202 | 92000 | Support | | 203 | 92000 | Support | | 204 | 91000 | Support |