Pori_IT

문제

Write a solution to select the product id, year, quantity, and price for the first year of every product sold.
Return the resulting table in any order. The result format is in the following example.

판매된 모든 제품에 대해서, 첫해의 가격, 연도, 제품ID, 수량을 선택하시오.

정렬 순서는 상관없다.

테이블 및 예제

[Sales]

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+

[Product]

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+

[Example]

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

풀이

RANK() 사용하기

제품 별로 첫해를 뽑아내기 위해서 제품의 판매 년도 순으로 rank를 생성한다.

SELECT product_id, year, quantity, price,
	RANK() OVER (PARTITION BY product_id ORDER BY year) AS row_num
FROM sales;

product_id 별로 그룹화를 하고, year 순으로 정렬을 해준 후에 rank를 생성한다.

| product_id | year | quantity | price | row_num |
| ---------- | ---- | -------- | ----- | ------- |
| 100        | 2008 | 10       | 5000  | 1       |
| 100        | 2009 | 12       | 5000  | 2       |
| 200        | 2011 | 15       | 9000  | 1       |

이 쿼리를 서브쿼리로 이용해서 rank(row_num)이 1인 경우를 선택하면 된다.

WITH mins AS (
    SELECT product_id, year, quantity, price,
    RANK() OVER (PARTITION BY product_id ORDER BY year) AS row_num
    FROM sales
)
SELECT m.product_id, m.year AS first_year, m.quantity, m.price
FROM mins AS m
WHERE m.row_num = 1;

문제

각각의 콘테스트에 대하여 등록된 사용자 비율을 구해서 출력합니다. (소수점 2자리 수 까지)

퍼센트는 내림차순, 콘테스트 아이디는 오름차순으로 정렬합니다.

테이블

[Users]

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| user_name   | varchar |
+-------------+---------+

[Register]

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| contest_id  | int     |
| user_id     | int     |
+-------------+---------+

[예제]

Input: 
Users table:
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 6       | Alice     |
| 2       | Bob       |
| 7       | Alex      |
+---------+-----------+
Register table:
+------------+---------+
| contest_id | user_id |
+------------+---------+
| 215        | 6       |
| 209        | 2       |
| 208        | 2       |
| 210        | 6       |
| 208        | 6       |
| 209        | 7       |
| 209        | 6       |
| 215        | 7       |
| 208        | 7       |
| 210        | 2       |
| 207        | 2       |
| 210        | 7       |
+------------+---------+
Output: 
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208        | 100.0      |
| 209        | 100.0      |
| 210        | 100.0      |
| 215        | 66.67      |
| 207        | 33.33      |
+------------+------------+

풀이

문제점은 단하나.

과연 어떻게 퍼센트를 구할 것인가? > 전체 유저의 수를 어떻게 가져와서 사용할 것인가

이게 문제였다. Join을 하고 Group을 묶으면 이를 확인하기 어려워지기 때문에 다른 방식을 찾아야했다.

그래서 SELECT에 서브쿼리를 추가하는 방법을 찾았다. 그냥 SELECT 안에 SELECT를 사용하는것이다!

코드는 다음과 같다.

SELECT
    contest_id,
    ROUND(
        100 * COUNT(DISTINCT user_id) / (SELECT count(user_id) FROM Users) 
        ,2) AS percentage
FROM Register
GROUP BY contest_id

하지만 아직 놓친 점이 하나 있다. 정렬을 해주어야한다. -> ORDER BY를 사용해서 원하는 컬럼을 오름차순, 내림차순으로 정렬한다.

SELECT
    contest_id,
    ROUND(
        100 * COUNT(DISTINCT user_id) / (SELECT count(user_id) FROM Users) 
        ,2) AS percentage
FROM Register

GROUP BY contest_id
ORDER BY percentage DESC, contest_id ASC

문제

5명 이상의 직속 부하가 있는 사람을 찾기

Write a solution to find managers with at least five direct reports.

-> direct reports는 직속 보고자, 자신이 관리하는 직원을 뜻한다고한다.

테이블

[Employee]

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| department  | varchar |
| managerId   | int     |
+-------------+---------+

[예제]

Input: 
Employee table:
+-----+-------+------------+-----------+
| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------+
Output: 
+------+
| name |
+------+
| John |
+------+

풀이

1. WITH AS 사용하여 서브쿼리 생성 -> 나의 풀이

우선 managerId에 manager name을 붙이기 위해서 자기 자신을 조인했다. 그리고 매니저 아이디로 그룹을 묶고 그룹에 얼마나 카운트가 되는지 확인하기 위해서 COUNT(e1.managerId)를 사용했다.

- managerId에 name 연결

- managerId >= 5 를 조사해야하기 때문에 managerId의 수를 세기

- GROUP BY 와 COUNT를 이용해서 각 매니저의 직속 부하 수를 셈

SELECT e2.name AS manager, COUNT(e1.managerId) AS cnt_manage
FROM Employee AS e1
JOIN Employee AS e2
    ON e1.managerId = e2.id
GROUP BY e1.managerId

| manager | cnt_manage |
| ------- | ---------- |
| John    | 5          |

!! 주의 !!

예제 케이스에는 하나의 경우만 존재하여 내 코드가 정답과 비슷해보이지만 아직 직속부하의 수에 대한 조건을 안걸었다.

따라서 위의 쿼리를 서브쿼리로 하고 WHERE 조건절을 걸어서 다음과 같이 코드를 완성했다.

WITH table1 AS (
    SELECT e2.name AS manager, COUNT(e1.managerId) AS cnt_manage
    FROM Employee AS e1
    JOIN Employee AS e2
        ON e1.managerId = e2.id
    GROUP BY e1.managerId
    )
SELECT manager AS name
FROM table1
WHERE cnt_manage > 4

| name |
| ---- |
| John |

2. HAVING, COUNT 사용

조금 더 간단하고 효율적인 방법은 없을까? 해서 찾아봤다. HAVING절을 이용하는 방법이 있어 가져왔다.

그전에 HAVING절에 대해서 조금 정리하면

HAVING 절은 GROUP BY를 통해 묶인 레코드 그룹에 대해서 조건을 거는 역할을 한다고 함!

[사용방법]

SELECT [columns] FROM [tables] GROUP BY [columns] HAVING [If] 이라고 합니다~

또한 HAVING 절에는 WHERE 절과 다르게 집계 함수의 사용이 가능합니다.

따라서 내 코드를 조금 수정 가능합니다. COUNT를 HAVING 절로 내리는 것입니다. -> 서브쿼리를 안해도 된다..!

SELECT e1.name
FROM Employee e1
JOIN Employee e2
ON e1.id = e2.managerId
GROUP BY e2.managerId
HAVING COUNT(e2.managerID) >=5

다음과 같이 수정 가능합니다.