ID 10299——Finding Updated Records【题目链接】
难度:Easy【简单】
公司:Microsoft【微软】
考察方式:Interview Questions【面试题目】
问题描述
Finding Updated Records【查找更新的记录】
We have a table with employees and their salaries, however, some of the records are old and contain outdated salary information. Find the current salary of each employee assuming that salaries increase each year. Output their id, first name, last name, department ID, and current salary. Order your list by employee ID in ascending order.
我们有一个包含员工及其薪水的表格,但是,有些记录已经过时并且包含过时的薪水信息。假设工资每年增加,求每个雇员的当前工资。输出他们的id, first name, last name, department_id以及 current salary。按员工 id 升序排列您的列表。
原始表
table:(ms_employee_salary)
解决方案
SQL方法
方法1
SELECT
id,
first_name,
last_name,
department_id,
MAX(salary) current_salary
FROM
ms_employee_salary
GROUP BY
id,
first_name,
last_name,
department_id
ORDER BY id ASC
方法2
SELECT
id,
first_name,
last_name,
department_id,
salary as max_salary
FROM (
SELECT *,
dense_rank() OVER (PARTITION BY id ORDER BY salary DESC) AS a
FROM ms_employee_salary) b
WHERE b.rnk = 1
ORDER by b.id ASC
方法3
SELECT DISTINCT
id,
first_name,
last_name,
department_id,
MAX(salary) OVER (partition by id) AS current_salary
FROM ms_employee_salary
ORDER BY id
Python方法
# Import your libraries
import pandas as pd
import numpy as np
# Start writing code
result = ms_employee_salary.groupby(['id','first_name','last_name','department_id'])['salary'].max().reset_index().sort_values('id')