오늘도 데이터분석

[문제]

department top three salaries 문제 바로 가기 

[풀이]

- 윈도우함수 DENSE_RANK() 이용

* 리트코드 MySQL은 윈도우함수가 안돼서 MSSQL로 풀이

SELECT Department
      ,Employee
      ,Salary
FROM (
SELECT de.name AS Department
     , em.name AS Employee
     , em.salary AS Salary
     , DENSE_RANK() OVER (PARTITION BY departmentID ORDER BY salary DESC) drank
FROM employee em
    INNER JOIN department de ON de.id = em.departmentID) rank
WHERE rank.drank <= 3

Employees Earning More Than Their Managers 문제 바로가기 

문제

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

풀이

-- employee 테이블에 managerID를 기준으로 SELF JOIN -> manager 테이블을 새로 만들기 

-- manager보다 salary가 employee 조건문 WHERE로 필터링

SELECT em.name AS Employee
FROM employee AS em
         INNER JOIN employee AS ma ON em.managerID = ma.id
WHERE em.salary > ma.salary

OUTPUT

{"headers": ["Employee"], "values": [["Joe"]]}

Customer Who Never Order 문제 바로가기 

문제 

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

문제 풀이

아무것도 주문하지 않은 고객의 이름을 추출하는 쿼리 입력

아무것도 주문하지 않음 = orders 테이블에 없는 고객

customers 테이블을 기준으로 orders를 LEFT JOIN 

SELECT customers.name AS Customers
FROM  customers
          LEFT JOIN orders ON customers.id = orders.customerID 
WHERE orders.id IS NULL  -- orders의 다른 컬럼으로 해도 동일 결과 orders.customerID IS NULL