Company Median Salary Rows

Hard

You are given an Employee table with columns: id: unique row identifier company: company label salary: salary value For each company, return the row(s) that sit at the median position after ordering that company's rows by: salary in ascending order id in ascending order when salaries tie Rules: If a company has an odd number of employees, return exactly one middle row. If a company has an even number of employees, return exactly two middle rows. Median selection must be based on ordered row positions, not on averaging salary values. Return exactly these columns: id, company, salary. The output row order can be arbitrary. Supported submission formats: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Employee:
| id | company | salary |
|----|---------|--------|
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |

Output

[
  {"id":5,"company":"A","salary":451},
  {"id":6,"company":"A","salary":513},
  {"id":12,"company":"B","salary":234},
  {"id":9,"company":"B","salary":1154}
]

Explanation

Company A has 6 rows, so positions 3 and 4 are selected after ordering by (salary, id). Company B also has 6 rows with medians at positions 3 and 4.

Example 2

Input

Employee:
| id | company | salary |
|----|---------|--------|
| 21 | C       | 500    |
| 22 | C       | 500    |
| 23 | C       | 800    |
| 24 | C       | 1200   |

Output

[
  {"id":22,"company":"C","salary":500},
  {"id":23,"company":"C","salary":800}
]

Explanation

All C rows ordered by (salary,id) are (500,id21), (500,id22), (800,id23), (1200,id24). For 4 rows, the middle positions are 2 and 3.

Example 3

Input

Employee:
| id | company | salary |
|----|---------|--------|
| 31 | D       | 700    |
| 32 | D       | 700    |
| 33 | D       | 700    |
| 34 | D       | 700    |
| 35 | D       | 700    |

Output

[
  {"id":33,"company":"D","salary":700}
]

Explanation

With 5 tied salaries, id ordering breaks ties, making id 33 the single median row.

Constraints

1 <= number of rows in Employee <= 100,000
Employee.id is unique
1 <= Employee.salary <= 1,000,000,000
company is a non-empty text label
Median ordering per company is by (salary ASC, id ASC)
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: id, company, salary

Employee: | id | company | salary | |----|---------|--------| | 1 | A | 2341 | | 2 | A | 341 | | 3 | A | 15 | | 4 | A | 15314 | | 5 | A | 451 | | 6 | A | 513 | | 7 | B | 15 | | 8 | B | 13 | | 9 | B | 1154 | | 10 | B | 1345 | | 11 | B | 1221 | | 12 | B | 234 |