Top-K Salaries Per Department
infosysFULLTIMEONSITE INTERVIEWProblem statement
The table employees contains employee salaries and department assignments. The table query_parameters contains exactly one row with the requested value k.
For each department, rank its distinct salary levels from highest to lowest using dense ranks. Return every employee whose salary rank is at most k, including all employees tied at a qualifying salary.
Return department_id, employee_id, employee_name, salary, and salary_rank. Order rows by department_id ascending, then salary descending, then employee_id ascending. Do not use LIMIT.
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
One row per employee.
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_idPK | Integer | No | — |
| employee_name | Text | No | — |
| department_id | Integer | No | — |
| salary | Integer | No | — |
query_parameters
Exactly one row containing the requested number of distinct salary levels.
| Column | Type | Nullable | Description |
|---|---|---|---|
| k | Integer | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| department_id | Integer | No | — |
| employee_id | Integer | No | — |
| employee_name | Text | No | — |
| salary | Integer | No | — |
| salary_rank | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
1 <= employees.length <= 200000employee_idis unique.department_id,employee_name, andsalaryare non-NULL.0 <= salary <= 1000000000query_parameterscontains exactly one row and1 <= k <= 100.