SQL and Pandas solution with explanation (SQL solution with Window function and nested inner join)
Reflection on Complexity\nI would like to highlight the intricate nature of the SQL query I have developed, which I consider to be among the most intricate in m
# Reflection on Complexity\nI would like to highlight the intricate nature of the SQL query I have developed, which I consider to be among the most intricate in my repertoire. While I am fully cognizant of the potential avenues for enhancement, I take a sense of accomplishment in my achievement.\n\nCommencing with a nested inner join SQL query, I subsequently recognized the viability of accomplishing the same objective leveraging the RANK() window function. It is noteworthy that upon transcribing the code utilizing the pandas library, the solution conspicuously manifested a heightened level of accessibility and simplicity.\n\nThe complete code for each is at the bottom. \n\n\n# Approach SQL Nested Inner Join (least time efficient solution)\n1. Determined the maximum salary within each department of the `Employee` table by utilizing the MAX() function in conjunction with the GROUP BY operation.\n    ```\n    SELECT MAX(salary) AS max_salary, departmentId\n        FROM Employee\n        GROUP BY departmentId\n    ```\n2. Performed an inner join between the outcomes of the initial step and the `Employee` table. This join is executed based on the criterion that an employee\'s salary corresponds to the highest salary within each department. This approach was undertaken to accurately account for scenarios where multiple individuals jointly held the top rank, ensuring their inclusion in the analysis.\n    ```\n    SELECT \n        Employee.departmentId as departmentId,\n        Employee.name as employee_name,\n        Employee.salary as salary\n    FROM Employee\n    INNER JOIN (\n        SELECT MAX(salary) AS max_salary, departmentId\n        FROM Employee\n        GROUP BY departmentId\n    ) AS highest_salary \n    ON highest_salary.departmentId = Employee.departmentId\n    WHERE Employee.salary = highest_salary.max_salary\n    ```\n3. Performed a subsequent inner join between the resultant table and the `Department` table, enabling the retrieval of the corresponding department names. Although it was feasible to execute this step initially, a strategic decision was made to defer it until later. This choice was driven by the anticipation that performing this join at the conclusion would be more time-efficient, owing to the comparatively reduced size of the intermediate table at that juncture.\n    ```\n    SELECT \n        Department.name as Department,\n        employees_highest_salary.employee_name as Employee,\n        employees_highest_salary.salary as Salary\n    FROM Department\n    INNER JOIN (\n        SELECT \n            Employee.departmentId as departmentId,\n            Employee.name as employee_name,\n            Employee.salary as salary\n        FROM Employee\n        INNER JOIN (\n            SELECT MAX(salary) AS max_salary, departmentId\n            FROM Employee\n            GROUP BY departmentId\n        ) AS highest_salary \n        ON highest_salary.departmentId = Employee.departmentId\n        WHERE Employee.salary = highest_salary.max_salary\n    ) as employees_highest_salary\n    ON Department.id = employees_highest_salary.departmentId;\n    ```\n\n# Approach SQL with Window Function\n1. Ranked the salary of every employee within each department of the `Employee` table by utilizing the RANK() Window function.\n    ```\n    RANK() OVER (PARTITION BY Employee.departmentId ORDER BY Employee.salary DESC) as salary_rank\n    ```\n2. Performed an inner join between the `Employee` table and the `Department` table to get the Department name based on the Department ID. \n    ```\n    SELECT\n        Department.name as department_name,\n        Employee.name as employee_name,\n        Employee.salary as employee_salary,\n        RANK() OVER (PARTITION BY Employee.departmentId ORDER BY Employee.salary DESC) as salary_rank\n    FROM Employee\n    JOIN Department ON Employee.departmentId = Department.id\n    ```\n3. Refined the results of step 2 to retain exclusively those instances where the rank was designated as 1, signifying the individuals with the highest remuneration.\n    ```\n    SELECT \n        EmployeeRanked.department_name as Department,\n        EmployeeRanked.employee_name as Employee,\n        EmployeeRanked.employee_salary as Salary\n    FROM (\n        SELECT\n            Department.name as department_name,\n            Employee.name as employee_name,\n            Employee.salary as employee_salary,\n            RANK() OVER (PARTITION BY Employee.departmentId ORDER BY Employee.salary DESC) as salary_rank\n        FROM Employee\n        JOIN Department ON Employee.departmentId = Department.id\n    ) as EmployeeRanked\n    WHERE salary_rank = 1;\n    ```\n# Approach Pandas (most optimized for time)\n1. Utilized pandas .merge() to merge the `Employee` table to the `Department` table \n    ```\n    employee.merge(department, left_on=\'departmentId\', right_on=\'id\')\n    ```\n2. Added a new column using .assign() and employing a lambda function to assign ranks to salary values subsequent to their aggregation by department. A crucial observation prompted the adjustment of the ranking method to "dense," thereby ensuring that individuals with identical salaries were accorded the same ranking.\n    ```\n    .assign(salary_rank=lambda x: x.groupby(\'departmentId\')[\'salary\'].rank(method=\'dense\', ascending=False))\n    ```\n3. Selected only the employees with highest remuneration.\n    ```\n    .query(\'salary_rank == 1\'\n    ```\n4. Kept only the columns with department name, employee\'s name and salary\n    ```\n    .loc[:, [\'name_y\', \'name_x\', \'salary\']]\n    ```\n5. An error consistently arose when attempting to rename the column "name_y" to "Department". However, the successful renaming of columns was achieved within the resultant DataFrame named "employee_ranked_df." The specific factor contributing to this disparity remains unclear.\n    ```\n    employee_ranked_df.rename(columns={\'name_y\': \'Department\', \'name_x\': \'Employee\', \'salary\': \'Salary\'})\n    ```\n\n# Complete Code\n## SQL Query Nested Inner Join (least optimized for time)\n```\nSELECT \n    Department.name as Department,\n    employees_highest_salary.employee_name as Employee,\n    employees_highest_salary.salary as Salary\nFROM Department\nINNER JOIN (\n    SELECT \n        Employee.departmentId as departmentId,\n        Employee.name as employee_name,\n        Employee.salary as salary\n    FROM Employee\n    INNER JOIN (\n        SELECT MAX(salary) AS max_salary, departmentId\n        FROM Employee\n        GROUP BY departmentId\n    ) AS highest_salary \n    ON highest_salary.departmentId = Employee.departmentId\n    WHERE Employee.salary = highest_salary.max_salary\n) as employees_highest_salary\nON Department.id = employees_highest_salary.departmentId;\n```\n\n## SQL Query with Window Function\n```\nSELECT \n    EmployeeRanked.department_name as Department,\n    EmployeeRanked.employee_name as Employee,\n    EmployeeRanked.employee_salary as Salary\nFROM (\n    SELECT\n        Department.name as department_name,\n        Employee.name as employee_name,\n        Employee.salary as employee_salary,\n        RANK() OVER (PARTITION BY Employee.departmentId ORDER BY Employee.salary DESC) as salary_rank\n    FROM Employee\n    JOIN Department ON Employee.departmentId = Department.id\n) as EmployeeRanked\nWHERE salary_rank = 1;\n```\n\n## Pandas Code (most optimized for time)\n```\nimport pandas as pd\n\ndef department_highest_salary(employee: pd.DataFrame, department: pd.DataFrame) -> pd.DataFrame:\n    # group by department\n    employee_ranked_df = (\n    employee\n    .merge(department, left_on=\'departmentId\', right_on=\'id\')\n    .assign(salary_rank=lambda x: x.groupby(\'departmentId\')[\'salary\'].rank(method=\'dense\', ascending=False))\n    .query(\'salary_rank == 1\')\n    .loc[:, [\'name_y\', \'name_x\', \'salary\']]\n)\n    return employee_ranked_df.rename(columns={\'name_y\': \'Department\', \'name_x\': \'Employee\', \'salary\': \'Salary\'})\n```\n
['Python', 'Python3', 'MySQL', 'MS SQL Server', 'Pandas']