KATA#79

codataffee·2024년 7월 2일

CODEKATA

목록 보기

79/114

WHAT IS KATA?

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

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

- SQL

오늘은 쿼리 테스트에 자주 사용된다는
TESTDOME 사이트에서 SQL 연습하기

✔️ 문제 해석

아래 작성된 쿼리의 결과로 나타나는 값 선택하기

쿼리 작동 순서

users 테이블과 friends 테이블 조인:
LEFT JOIN이므로 users 테이블 기준
users.id가 friends.user1 또는 friends.user2와 일치하는 행이 붙게 된다.

조인 결과:
| users.id | users.name | users.sex | friends.user1 | friends.user2 |
|----------|------------|-----------|---------------|---------------|
| 1        | Ann        | NULL      | 1             | 2             |
| 1        | Ann        | NULL      | 1             | 3             |
| 2        | Steve      | m         | 1             | 2             |
| 2        | Steve      | m         | 2             | 3             |
| 3        | Mary       | f         | 1             | 3             |
| 3        | Mary       | f         | 2             | 3             |
| 4        | Brenda     | f         | NULL          | NULL          |


조건 필터링:
WHERE users.sex = 'f': 성별이 여성('f')인 사용자만 선택

필터링 결과:
| users.id | users.name | users.sex | friends.user1 | friends.user2 |
|----------|------------|-----------|---------------|---------------|
| 3        | Mary       | f         | 1             | 3             |
| 3        | Mary       | f         | 2             | 3             |
| 4        | Brenda     | f         | NULL          | NULL          |



그룹화 및 카운트:
GROUP BY users.id, users.name: 사용자 ID와 이름별로 그룹화
COUNT(*) as count: 각 그룹에 있는 행의 수 계산

최종 결과:
| name   | count |
|--------|-------|
| Mary   | 2     |
| Brenda | 0     |

✔️ 제출

✔️ 문제: Regional Sales Comparison

결과 예시 링크

✔️ 문제 해석

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

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

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

✔️ 제출

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