[17주차_SQL] SQL 풀이 모음

[yeongleej]

추천 문제와 전 주의 본인 순번 전 사람이 풀었던 문제입니다.

고다혜:
1321. Restaurant Growth
3220. Odd and Even Transactions

배수빈:
570. Managers with at Least 5 Direct Reports
1084. Sales Analysis III

백제완:
1934. Confirmation Rate
185. Department Top Three Salaries

이지영:
608. Tree Node
1341. Movie Rating

이혜원:
180. Consecutive Numbers
607. Sales Person

## 문제: [문제명](링크)


### 설명


### 코드


### 아이디어

[baexxbin]

문제: 570. Managers with at Least 5 Direct Reports

설명

• 최소 5명의 직속 부하 직원이 있는 관리자를 찾기
• Employee 테이블 한 개 존재

코드

SELECT a.name AS name
FROM Employee a
JOIN Employee b ON a.id = b.managerId
GROUP BY a.id, a.name
HAVING COUNT(b.id) >= 5;

아이디어

• 셀프 조인
  • 매니저를 나타내는 a테이블
  • 직원을 나타내는 b테이블
  • 직원이(a.id) 직원의 매니저(b.managerId)와 일치하는 직원 연결
• GROUP BY
  • 각 매니저가 관리하는 직원을 하나의 그룹으로 묶기
• HAVING
  • 각 매니저가 관리하는 직원 수(b.id) 계산

🙀 헬프

JOIN Employee b ON a.id = b.managerId

엄청 기초개념이긴 한데,, 위처럼 JOIN을 했을때 왜 결과가 이렇게 나오는지 잘 이해가 안가요.. 전 반대로 위 쿼리는 a가 직원테이블, b가 매니저 테이블이 될거라고( 직원테이블 먼저, 매니저 테이블 형태로) 생각했는데 왜 위와 같이 a 매니저 테이블, b직원 테이블 형태로 나오는거죠...ㅠㅠ

[yeongleej]

문제: 608. Tree Node

설명

• 각 노드가 [ "Root", "Inner", "Leaf" ] 인지 출력
  • "Root" : 부모가 없는 루트 노드
  • "Leaf" : 자식이 없는 리프 노드
  • "Inner" : 루트노드도 아니고, 리프 노드도 아닌 노드

코드

SELECT p.id,
       CASE
        WHEN p.p_id IS NULL THEN "Root"
        WHEN c.id IS NULL THEN "Leaf"
        ELSE "Inner"
       END AS type
FROM tree p
LEFT JOIN tree c
ON p.id = c.p_id
GROUP BY p.id

아이디어

• LEFT JOIN 으로 각 노드의 자식 정보 출력
  • 기준 트리 => tree p, 자식 정보 트리 => tree c
• 노드의 p_id가 null이면 부모가 없으므로 "Root"노드
• 노드의 c.id가 null이면 자식이 없으므로, "Leaf" 노드
• 그 외의 노드들은 "Inner" 노드

[icegosimperson]

문제: Consecutive Numbers

설명

• 연속해서 최소 3번 나타나는 모든 숫자 찾기

코드

SELECT DISTINCT a.num AS COnsecutiveNums
FROM LOGS a
JOIN LOGS b ON a.id = b.id -1
JOIN LOGS c ON b.id = c.id-1
WHERE a.num = b.num AND b.num = c.num;

아이디어

• SELF JOIN : 연속한 숫자를 찾기
  • SELF JOIN을 두번 수행하여 3번 연속하는 모든 숫자를 찾기
  • a.num = b.num AND b.num = c.num
• DISTINCT : 중복 제거

[KodaHye]

문제: Odd and Even

설명

• 날짜별로 매출액을 홀수, 짝수 나누어서 합계 구하기

코드

SELECT
      transaction_date
    , SUM(CASE WHEN MOD(amount, 2) = 0 THEN 0 ELSE amount END) AS odd_sum
    , SUM(CASE WHEN MOD(amount, 2) = 1 THEN 0 ELSE amount END) as even_sum
FROM TRANSACTIONS
GROUP BY transaction_date
ORDER BY transaction_date ASC

아이디어

• 짝수에 대한 합계를 구할 때: amount가 홀수라면 0으로 만들기
• 홀수에 대한 합계를 구할 때: amount가 짝수라면 0으로 만들기
• transaction_date에 대해서 group by를 해서 위 두 칼럼의 합계를 구하고 정렬하기

[baexxbin]

문제: 1084. Sales Analysis III

설명

• 2019년 첫번째 분기에만 팔린 상품 고르기

코드

SELECT p.product_id, p.product_name
FROM Product p
JOIN Sales s ON p.product_id = s.product_id
GROUP BY p.product_id
HAVING MIN(sale_date) >= '2019-01-01' AND MAX(sale_date) <= '2019-03-31';

아이디어

• 조건에 맞는 컬럼 출력하기 위해 두 테이블 조인
• p.product_id를 기준으로 그룹 묶기
  • 1-3월에 해당하는 분기로 조건걸기
  • 이때, p.product_id에 여러 날짜가 있으니 최소, 최대 일이 1분기에 들어오는지 확인

[icegosimperson]

문제: Sales Person

설명

• RED라는 이름을 가진 회사와 관련된 주문이 없는 모든 영업 사원의 이름을 찾는 솔루션 작성

코드

SELECT name FROM SalesPerson
WHERE sales_id NOT IN(
    SELECT sales_id
    FROM Orders a JOIN Company b
    ON a.com_id = b.com_id AND b.name = 'RED'
)

아이디어

• 'RED'를 포함한 orders.com_id = Company.com_id 가져옴
• NOT IN : 서브쿼리에서 반환된 값 목록과 비교하여 해당 값이 목록에 포함되지 않은 경우 출력

[KodaHye]

문제: Restaurant Growth

설명

• 사용자 별 식당 방문 날짜와 결제 금액이 있는 CUSTOMER 테이블을 통해 각 날짜별 7일 동안의 매출 합계와 평균 구하기

코드

# AMOUNT OVER SUM TABLE
WITH AOST AS (
    SELECT 
        visited_on,
        SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) `amount`,
        MIN(visited_on) OVER() min_date
    FROM CUSTOMER
)

SELECT 
    DISTINCT(visited_on)
    , amount
    , round(amount / 7, 2) `average_amount`
FROM AOST
WHERE min_date + 6 <= visited_on

아이디어

• 임시 테이블에 통해 현재 날짜 이전 7일 동안의 amount 합계 구한거 저장하기

  • amount
    • SUM(amount) OVER(ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) amount``
      • MYSQL에서 날짜 또는 시간 기반의 윈도우 함수 사용은 ROW절과 함꼐 구현하거나, 별도의 계산을 통해 처리해야 됨 (by GPT)
      • ORDER BY visited_on RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
        • CURRENT ROW의 visited_on 값을 기준으로 현재 행의 날짜에서 6일 이전까지의 범위를 지정함
    • min_date
      • customer 테이블에 저장되어 있는 가장 오래된 visited_on 날짜 저장

• DISTINCT를 통해 유일한 날짜에 대해서 나타내고, 7로 나누고 반올림해서 원하는 값 출력

[yeongleej]

문제: Movie Rating

설명

• 영화 테이블 : Movies
• 유저 테이블 : Users
• 평점 테이블 : MovieRating
  => 평점을 가장 많이 남긴 사람 이름과 2022년 2월에 평균 평점이 가장 높은 영화 이름 출력하기
  => 평점을 가장 많이 남긴 사람과 + 2022년 2월에 평균 평점이 가장 높은 영화 출력

코드

SELECT name AS results
FROM MovieRating a 
JOIN Users b 
ON a.user_id = b.user_id
GROUP BY name
ORDER BY COUNT(*) DESC, name
LIMIT 1

UNION ALL

SELECT title AS results
FROM MovieRating a
JOIN Movies b
ON a.move_id = b.move_id
WHERE created_at like "2022-02%"
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1;

아이디어

• 다른 정보들을 가져와서 출력해야 하므로 UNION ALL 활용
  • 가장 많은 리뷰를 남긴 사람 출력
    • MoveRating과 Users JOIIN
  • 2022-02에 평점이 가장 높은 영화 출력
    • MoveRating과 Movies JOIN