Highest Salary in Each Department in Python

An interviewer asks: "For each department, list the employee with the highest salary." The trap is a tie: when two people share the top salary, both belong in the answer. The check adds this employees table to shop.db:

id | name       | department | salary
1  | Mia Costa  | Sales      | 4200
2  | Leo Marin  | Warehouse  | 3400
3  | Ivo Petrov | Support    | 2900
4  | Ada Lund   | Sales      | 3900
5  | Sam Okafor | Warehouse  | 3400
6  | Zoe Kim    | Support    | 3100
7  | Tom Berg   | Warehouse  | 3100

RANK() OVER (PARTITION BY …) numbers the rows from 1 again in each group, and equal values share a rank. Here it ranks each customer's orders from newest to oldest:

SELECT customer_id, id, ordered,
       RANK() OVER (PARTITION BY customer_id ORDER BY ordered DESC) AS newest
FROM orders

You can't filter a window function in the WHERE of its own SELECT, so you rank in a CTE and keep rank 1 in the main query. Interviewers then ask for a second way: a subquery with each department's MAX(salary), joined back to employees on the department and the salary. Both queries must print the same 4 rows (the join keeps table order):

department | name | salary
Sales | Mia Costa | 4200
Support | Zoe Kim | 3100
Warehouse | Leo Marin | 3400
Warehouse | Sam Okafor | 3400
(4 rows)
department | name | salary
Sales | Mia Costa | 4200
Warehouse | Leo Marin | 3400
Warehouse | Sam Okafor | 3400
Support | Zoe Kim | 3100
(4 rows)

Your task: in query, rank each department's employees by salary in a CTE named ranked and keep the rows ranked 1; in join_query, join employees to a subquery tops with each department's top salary.

Assignment
Follow the Coding Tutorial to write both queries, one clause per line.

Hint
Use RANK, not ROW_NUMBER: ROW_NUMBER would number Leo and Sam 1 and 2 and drop one of them. In the join, match the department too: Tom's 3100 is Support's top salary.