[MySQL] 그룹 스터디 6일 차 (11/18)

Leetcode 《 Find Total Time Spent by Each Employee》

@ Easy

@ Pandas30

https://leetcode.com/problems/find-total-time-spent-by-each-employee/description/?envType=study-plan-v2&envId=30-days-of-pandas&lang=pythondata

Write a solution to calculate the total time in minutes spent by each employee on each day at the office. Note that within one day, an employee can enter and leave more than once. The time spent in the office for a single entry is out_time - in_time.

Return the result table in any order.

import pandas as pd

def total_time(employees: pd.DataFrame) -> pd.DataFrame:
    df = employees.groupby(['emp_id', 'event_day'], as_index = False).sum()
    df['total_time'] = df['out_time'] - df['in_time']
    df.rename(columns={'event_day':'day'}, inplace = True)
    return df[['day', 'emp_id', 'total_time']]

import pandas as pd

def total_time(employees: pd.DataFrame) -> pd.DataFrame:
    employees['total_time'] = employees['out_time'] - employees['in_time']
    result = employees.groupby(['event_day','emp_id'])['total_time'].sum().reset_index()
    result = result.rename(columns={'event_day':'day'})
    return result

 

 

Leetcode 《 Department Top Three Salaries》

@ Hard

@ SQL50

https://leetcode.com/problems/department-top-three-salaries/?envType=study-plan-v2&envId=top-sql-50

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department. Write a solution to find the employees who are high earners in each of the departments. Return the result table in any order.

The result format is in the following example.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+​

SELECT Department, Employee, Salary
FROM (
    SELECT
        D.name AS 'Department',
        E.name AS 'Employee',
        salary AS 'Salary',
        DENSE_RANK() OVER(PARTITION BY E.departmentid ORDER BY salary DESC) AS rnk
    FROM employee E
    LEFT JOIN department D ON E.departmentid = D.id
) t
WHERE rnk <= 3

 

 

Leetcode 《 Department Top Three Salaries》

@ Easy

@ SQL50

https://leetcode.com/problems/replace-employee-id-with-the-unique-identifier/

Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null. Return the result table in any order. The result format is in the following example.

+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+​

SELECT unique_id, name
FROM employees a
LEFT JOIN employeeuni b ON a.id = b.id

 

 

프로그래머스 《 언어별 개발자 분류하기》

@ Level 4

@ GROUP BY

이것만 너무 길어서 따로 적은 글 ~

[MySQL] 프로그래머스 《언어별 개발자 분류하기》 - 비트 연산자

 

 

 

프로그래머스 《언어별 개발자 분류하기》 

프로그래머스 《언어별 개발자 분류하기》