Simple Using RANK() function🌻
Intuition\nThe query selects the Department name, Employee name, and Salary from a derived table (t1) that includes the Department name, Employee name, Salary, 
# Intuition\nThe query selects the Department name, Employee name, and Salary from a derived table (t1) that includes the Department name, Employee name, Salary, and the rank of each employee within their department based on salary in descending order. The outer query then filters the results to only include rows where the rank is 1, indicating the highest salary in each department.\n\n# Approach\nThe approach involves creating a derived table (t1) using a JOIN operation between the Department and Employee tables. The RANK() function is applied within each department partition to assign a rank to each employee based on their salary. The outer query then filters the results to only include rows with a rank of 1.\n\n# Complexity\n- Time complexity:\nThe time complexity is determined by the JOIN operation, the RANK() window function, and the final WHERE clause. Assuming appropriate indexing, the JOIN operation and RANK() function would have a time complexity of O(n log n), where n is the number of rows in the combined Department and Employee tables. The final WHERE clause does not significantly impact the time complexity.\n\n- Space complexity:\nThe space complexity is influenced by the temporary storage required for the derived table (t1), which includes the Department name, Employee name, Salary, and RANK(). Assuming m is the number of rows in the derived table, the space complexity is O(m). The RANK() function requires additional storage for the ranking information within each partition.\n\n# Code\n```\n# Write your MySQL query statement below\nSELECT t1.Department, t1.Employee, t1.Salary\nFROM(SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary\n,RANK()OVER(PARTITION BY d.id ORDER BY salary DESC) AS rk\nFROM Department AS d\nJOIN Employee AS e ON E.departmentId = d.id) AS t1\nWHERE rk = 1\n```
['MySQL']