The salary in the middle

Problem

In Sales the average salary is 2600, but two of its three people earn a good deal less: it's pulled up by Mark, the manager. HR wants a number that a single high salary can't move: the median. You line up the department's salaries from lowest to highest and take the one in the center; with an even number there is no center, and you average the two in the middle.

Show each department's name and the median of its salaries, in that order. For Sales it's 2100. A department with nobody in it doesn't show up, and Carol, who has no department, isn't in any line.

Sort from the highest median to the lowest; if two tie, by the department's name.

What it has to return

This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.

You start with this

SELECT dept_id, name, salary
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary