Stars Within Their Team

Kestrel Labs has a small raise pool this year and has decided the fairest way to spend it is on people who out-earn their own team's average — the ones already carrying the higher end of their team's range.

Note carefully what that is not. Beating the company average is a different test, and it has a systematic bias: it hands the whole pool to whichever teams are paid best overall and gives the well-regarded Marketing lead nothing. Each person here has to be measured against their own team, which means the threshold is not one number but a different number for every row.

employees — one row per person. department_id is the team they sit on, given as the matching id in departments; salary is annual pay in dollars; hire_date is the day they started, written year-month-day; manager_id is the id of the person they report to, and is NULL for anyone who reports to nobody.

The table already exists in the database — there is nothing to create or load.

Task: Write a query returning three columns, name, department_id and salary, one row per employee paid strictly more than the average salary of their own team — equal to that average does not qualify. Order the rows by department_id ascending, and within a team by salary descending. The employee's own salary counts toward their team's average.

Example output — shape only, on invented people. A team can contribute more than one row, and inside a team the better paid comes first: