2025.03.19 본_캠프 21일차

102~106번 SQL 문제 정리

---

102번 - 최소 5명의 학생이 있는 클래스 찾기

문제 설명:

• Courses 테이블에서 학생이 5명 이상 등록된 클래스를 찾는 문제

  내 정답 쿼리:

SELECT class    
FROM (
    SELECT class, COUNT(student) OVER (PARTITION BY class) AS cnt
    FROM Courses
) a
WHERE cnt >= 5
GROUP BY class;

쿼리 분석:

• COUNT(student) OVER (PARTITION BY class) 를 사용하여 각 클래스별 학생 수를 계산
• 학생 수(cnt)가 5 이상인 클래스만 선택

---

103번 - 각 유저의 팔로워 수 구하기

문제 설명:

• Followers 테이블에서 각 유저의 팔로워 수를 구하는 문제

• 결과는 user_id 기준 오름차순 정렬

  내 정답 쿼리:

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id;

쿼리 분석:

• COUNT(follower_id) 를 사용하여 각 user_id 의 팔로워 수를 계산
• GROUP BY user_id 로 그룹화 후 ORDER BY user_id 로 정렬

---

104번 - 가장 큰 단일 숫자 찾기

문제 설명:

• MyNumbers 테이블에서 한 번만 등장한 숫자 중 가장 큰 값을 찾는 문제

• 단일 숫자가 없으면 NULL 반환

  내 정답 쿼리:

SELECT MAX(num) AS num
FROM (
    SELECT num, COUNT(num) OVER (PARTITION BY num) AS cnt
    FROM MyNumbers
) a
WHERE cnt = 1;

쿼리 분석:

• COUNT(num) OVER (PARTITION BY num) 를 사용하여 각 숫자의 등장 횟수를 계산
• cnt = 1 조건을 만족하는 숫자들 중 MAX(num) 을 선택하여 가장 큰 값 반환

---

105번 - 모든 상품을 구매한 고객 찾기

문제 설명:

• Customer 테이블에서 모든 Product 테이블의 상품을 구매한 고객 찾기

내 정답 쿼리:

WITH a AS (
    SELECT C.customer_id, P.product_key
    FROM Customer C JOIN Product P ON C.product_key = P.product_key
)
SELECT customer_id
FROM a
GROUP BY customer_id
HAVING (SELECT COUNT(*) FROM Product) = COUNT(DISTINCT product_key);

쿼리 분석:

• Customer 와 Product 를 JOIN 하여 구매 내역 생성
• GROUP BY customer_id 로 고객별 구매한 상품 개수 비교
• HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product) 조건으로 모든 상품 구매한 고객 필터링

---

106번 문제 - 매니저와 보고 직원 정보 조회

문제 설명:

회사의 직원 정보가 담긴 Employees 테이블에서 매니저(다른 직원이 보고하는 직원)의 정보를 조회해야 한다.
매니저의 정보에는 다음이 포함된다.

1. employee_id - 매니저의 ID
2. name - 매니저의 이름
3. reports_count - 해당 매니저에게 직접 보고하는 직원 수
4. average_age - 보고하는 직원들의 평균 나이 (반올림하여 정수로 출력)

매니저는 최소 한 명 이상의 직원이 reports_to로 설정된 직원들이다.
결과는 employee_id 기준으로 정렬해야 한다.

---

틀린 내 정답 SQL

WITH A AS (
    SELECT DISTINCT reports_to
    FROM Employees
    WHERE reports_to IS NOT NULL
)
SELECT e.employee_id, e.name, COUNT(e.reports_to) AS reports_count, 
       ROUND(AVG(e.age)) AS average_age 
FROM Employees e JOIN A ON e.employee_id = A.reports_to 
WHERE 
GROUP BY e.employee_id, e.name; 

---

이렇게 하면 A서브쿼리는 단순히 매니저 ID만 뽑아 놓은것이다.
그럼 FROM Employees e JOIN A ON e.employee_id = A.reports_to를 하게 되면 그냥 A에 있는 매니저 ID를 기준으로 Employees에서 해당 ID를 조회하는 것뿐이 된다.

• 문제 요구사항인 매니저가 몇 명에거 보고받는지를 확인 해야 함

여기서 발생한 문제점 : A는 단순히 매너지 ID만 저장된 테이블이라 ,reports_to가 누구인지 모름

• FROM Employees e JOIN A ON e.employee_id = A.reports_to 를 하면, A에 존재하는 매너지 ID를 가진 직원들만 필터링 됨 -> 즉, reports_count를 정확히 하는게 불가

• 올바린 JOIN 형식
  - 매니저를 기준으로 그에게 보고하는 직원들을 JOIN 해야 정확한 reports_count와 average_age를 구할 수 있음.

  • 즉, FROM Employees e JOIN Employees r ON e.employee_id = r.reports_to가 되야 함
    e.employee_id가 매니저 ID이고, r.employee_id가 해당 매니저에거 보고하는 직원 ID이기 때문.

---

쿼리 분석

1. LEFT JOIN을 사용하여 reports_to를 기준으로 직원과 그들의 직속 보고 직원들을 연결한다.
2. COUNT(r.employee_id) OVER(PARTITION BY e.employee_id)를 사용하여 매니저별 보고 직원 수를 계산한다.
3. AVG(r.age) OVER(PARTITION BY e.employee_id)를 사용하여 매니저별 보고 직원의 평균 나이를 계산하고 ROUND()로 반올림한다.
4. WHERE reports_count > 0 조건을 추가하여 매니저만 선택한다.
5. ORDER BY employee_id로 결과를 정렬한다.