Company Median Salary Rows

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employee =

[{"id":1,"company":"A","salary":2341},{"id":2,"company":"A","salary":341},{"id":3,"company":"A","salary":15},{"id":4,"company":"A","salary":15314},{"id":5,"company":"A","salary":451},{"id":6,"company":"A","salary":513},{"id":7,"company":"B","salary":15},{"id":8,"company":"B","salary":13},{"id":9,"company":"B","salary":1154},{"id":10,"company":"B","salary":1345},{"id":11,"company":"B","salary":1221},{"id":12,"company":"B","salary":234},{"id":13,"company":"C","salary":2345},{"id":14,"company":"C","salary":2645},{"id":15,"company":"C","salary":2645},{"id":16,"company":"C","salary":2652},{"id":17,"company":"C","salary":65}]

Company Median Salary Rows

Hints

Company Median Salary Rows

Hints