Rank Within the Team

Managers at Kestrel Labs keep asking the wrong version of the same question. They want to know how one of their people sits on pay, and the company-wide answer is useless to them: Engineering pay and Marketing pay are set by two different markets, so the best-paid person in Marketing lands mid-table on an all-company list and looks ordinary.

The comparison has to restart at every team boundary, and every employee has to come back — including Farid Amiri, who is the whole of Marketing and is therefore first on his team by default.

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 dept_rank (that person's standing by pay inside their own team, 1 being the best paid on that team), one row for every employee. Two teammates on the same salary share a number and the number straight after it is skipped, so a team can read 1, 2, 2, 4. Order the rows by department_id ascending, then by dept_rank ascending. No two teammates are paid the same here, so that fixes the sequence.

Example output — shape only, on invented people. Two teammates on team 21 are paid the same, so they share position 2, position 3 goes unused, and the next person is on 4: