KATA#80

codataffee·2024년 7월 3일

CODEKATA

목록 보기

80/114

WHAT IS KATA?

KATA는 기술과 기술 향상에 초점을 맞춘 코드 챌린지입니다.
일부는 프로그래밍 기본 사항을 교육하는 반면 다른 일부는 복잡한 문제 해결에 중점을 둡니다.

이 용어는 The Pragmatic Programmer 라는 책의 공동 저자인 Dave Thomas 가
무술에서 일본의 카타 개념을 인정하면서 처음 만들어졌습니다.
Dave의 개념 버전은 코드 카타를 프로그래머가
연습과 반복을 통해 기술을 연마하는 데 도움이 되는 프로그래밍 연습으로 정의합니다.

- SQL

✔️ 문제: Regional Sales Comparison

결과 예시 링크

✔️ 문제 해석

지역 판매 분석 보고서 쿼리 작성 :

지역 이름
지역의 직원당 평균 판매량
해당 지역의 평균 판매량과 최고 평균 판매량 간의 차이

예외 상황 처리
직원이 없는 지역도 포함.
직원이 있지만 판매가 없는 경우에도 평균 판매량을 0으로 계산

✔️ 제출 #1

WITH ALLINFO AS (
  SELECT sl.id,
         sl.amount,
         e.name empname,
         st.name stname,
         r.name rname
  FROM regions r
       LEFT JOIN states st ON r.id = st.regionId
       LEFT JOIN employees e ON st.id = e.stateId
       LEFT JOIN sales sl ON e.id = sl.employeeId
  ),
  REGIONSALE AS (
  SELECT rname,
         COALESCE(SUM(amount), 0) totalsales,
         COUNT(DISTINCT empname) totalemp
  FROM ALLINFO
  GROUP BY 1
  ),
  REGIONAVG AS (
  SELECT rname,
         CASE WHEN totalemp = 0 THEN 0 ELSE totalsales * 1.0 / totalemp END avgsales
  FROM REGIONSALE
  ),
  MAXAVG AS (
  SELECT MAX(avgsales) maxavgsale
  FROM REGIONAVG
  )
  
SELECT rname name,
       avgsales average,
       ABS((SELECT maxavgsale FROM MAXAVG) - avgsales) difference
FROM REGIONAVG

예시와 값은 같게 나오는데, 통과가 안된다..

✔️ 제출 #2

튜터님께 튜터링 후 통과가 되지 않는 원인을 파악했다.
원인은 employee (직원)이 없는 경우 (NULL)에 대한 처리를 하지 않았다는 것.

추가로 필요 없는 CTE 를 한 테이블에서 해결할 수 있었다.
(기존) 지역별 판매량 > 지역별 판매량 평균 / (수정) 지역별 판매량 평균 한번에 구하기

WITH ALLINFO AS (
  SELECT sl.id,
         sl.amount,
         e.name empname,
         st.name stname,
         r.name rname
  FROM regions r
       LEFT JOIN states st ON r.id = st.regionId
       LEFT JOIN employees e ON st.id = e.stateId
       LEFT JOIN sales sl ON e.id = sl.employeeId
  ),
  REGIONAVG AS (
  SELECT rname,
         IFNULL(SUM(amount) / COUNT(DISTINCT empname), 0) avgsales
  FROM ALLINFO
  GROUP BY 1
  ),
  MAXAVG AS (
  SELECT MAX(avgsales) maxavgsale
  FROM REGIONAVG
  )
  
SELECT rname name,
       avgsales average,
       ABS((SELECT maxavgsale FROM MAXAVG) - avgsales) difference
FROM REGIONAVG
ORDER BY 1

IFNULL() 함수를 사용해서
empname이 NULL인 경우 (즉, 지역에 해당하는 직원이 없을 경우) 0을 반환시켜주었다.

통과 !

✔️ 문제 #2: Challenges

Hackers Table :Challenges Table :

문제
각 학생이 만든 챌린지의 총 개수 계산
총 챌린지 개수 기준으로 내림차순 정렬
동일한 챌린지 개수를 만든 학생이 여러 명일 경우 hacker_id로 오름차순 정렬
가장 많은 챌린지 개수를 만든 학생들은 모두 결과 포함
가장 많은 챌린지 개수보다 적은 챌린지 개수를 동일하게 만든 학생들은 결과에서 제외

✔️ 제출 쿼리

✔️ 쿼리 분석

SELECT c.hacker_id,
       h.name,
       COUNT(c.challenge_id) challenges_created 
FROM Challenges c
     LEFT JOIN Hackers h ON c.hacker_id = h.hacker_id
     # 챌린지와 해커 테이블을 조인하여 각 해커가 만든 챌린지 수 계산
GROUP BY 1, 2
# 해커 ID와 이름 기준 그룹화
HAVING COUNT(c.challenge_id) = (
                               SELECT MAX(total_challenges) 
                               FROM (
                                    SELECT COUNT(challenge_id) total_challenges 
                                    FROM Challenges GROUP BY hacker_id ) a
                                    # 가장 많은 챌린지를 만든 해커를 선택하거나,
                                    ) OR 
                                    COUNT(c.challenge_id) IN 
                                       ( 
                                       SELECT total_challenges 
                                       FROM (
                                            SELECT COUNT(challenge_id) total_challenges 
                                            FROM Challenges 
                                            GROUP BY hacker_id 
                                            ) b 
                                       GROUP BY total_challenges 
                                       HAVING COUNT(*) = 1
                                       # 동일한 챌린지 개수를 가진 해커가 한 명만 있는 경우 선택
                                       ) 
ORDER BY challenges_created DESC,
         c.hacker_id
# 챌린지 개수 기준 내림차순 정렬, 
# 같은 경우 해커 ID 기준 오름차순 정렬