[16주차_SQL] SQL 풀이 모음

[yeahdy]

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

고다혜:
570. Managers with at Least 5 Direct Reports
연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

배수빈:
1934. Confirmation Rate
177. Nth Highest Salary

백제완:
3374. First Letter Capitalization II

이지영:
180. Consecutive Numbers
1341. Movie Rating

이혜원:
1321. Restaurant Growth
그룹별 조건에 맞는 식당 목록 출력하기

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


### 설명


### 코드


### 아이디어

[icegosimperson]

문제: Restaurant Growth

설명

• 지난 7일 동안 방문 날짜 별로 고객 방문 횟수 합과 평균 구하기
• 문제에서 주어진 계산 : 고객이 7일 기간(현재 날짜 + 6일 전) 동안 지불한 금액의 이동 평균을 계산
• 7일 평균 ROUND(,2)
• visited_on 오름차순 정렬

코드

SELECT visited_on,
    (
        SELECT SUM(amount) FROM customer 
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
        SELECT SUM(amount)/7 # 7일 평균 계산
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on # 현재 행의 방문 날짜 - 6일 전
        ), 2 # 반올림 조건
    )AS average_amount
FROM customer c
WHERE visited_on >= (
    SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY) # 기준 날짜(2019-01-01, MIN(visited_on)에 (+7일) 2019-01-07부터
    FROM Customer
)
GROUP BY visited_on # 방문 날짜별로 결과를 그룹화
ORDER BY visited_on; # 오름차순 정렬

아이디어

• INTERVAL : SQL에서 날짜 및 시간 연산을 수행
  • 날짜나 시간 값에 특정 기간을 더하거나 뺄 때 사용된다.
  • DATE_ADD : 기준 날짜(2019-01-01, MIN(visited_on)에 (+7일) 2019-01-07부터 계산
  • DATE_SUB : 현재 행의 방문 날짜 - 6일 전 계산

[KodaHye]

문제: Managers with at Least 5 Direct Reports

설명

• id, name, department, managerId가 있는 테이블이 있을 때, 자신이 담당하는 직원의 수가 5명 이상인 사원의 이름 출력

코드

SELECT a.name
FROM EMPLOYEE a
RIGHT JOIN EMPLOYEE b
ON a.id = b.managerId
GROUP BY  a.id
HAVING COUNT(a.id) >= 5

아이디어

• id와 managerId에 대해서 inner join을 해서 각 사람을 맡는 매니저가 누구인지 확인
• a.id에 대해 GROUP BY, COUNT 연산을 해서 담당하는 인원이 5명 이상인 매니저를 추출함

left join 후 조건 절을 통해 원하는 결과를 추출할 수 있지만, 대량의 데이터에서는 inner 조인이 아닌 left join 후 where절을 사용하면 더 큰 비용이 들 수 있음. (상황에 맞게 inner join 사용하기)

[baexxbin]

문제: Confirmation-rate

설명

• 사용자의 확인율은 '확인'된 메시지의 수를 요청된 확인 메시지의 총 수로 나눈 값입니다.
• 확인 메시지를 요청하지 않은 사용자의 확인율은 0입니다.
• 확인율을 소수점 두 자리로 반올림합니다.

코드

SELECT s.user_id,
        ROUND(IFNULL(AVG(c.action="confirmed"),0),2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id

아이디어

• 모든 사용자에 대한 값을 찾기 위해 LEFT JOIN 진행
  • Confirmations에는 없는 사용자 있을 수 있음
• 사용자 별로 계산하기 위해 GROUP BY 사용
• 조건에 맞는 비율을 계산하기 위해 IFNULL 사용
  • 앞서 LEFT JOIN시 NULL값이 있을 수 있음
  • AVG와 NULL
    • NULL값이 있을 경우, NULL값을 포함하지 않고 계산하거나 모든 값이 NULL일 경우 결과도 NULL

[KodaHye]

문제: 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

설명

• HR_DEPARTMENT(부서 ID, 국문부서명, 영문부서명, 부서위치)
• HR_EMPLOYEES(사번, 성명, 부서ID, 직책, 이메일, 전화번호, 입사일, 연봉)
• HR_GRADE(사번, 연도, 반기, 평가점수)

기준점수	평가 등급	성과금(연봉 기준)
96이상	S	20%
90이상	A	15%
80이상	B	10%
이외	C	0%

사번, 성명, 평가 등급, 성과금을 조회하는 SQL문 작성

코드

WITH GRADE_TABLE AS (
    SELECT
        EMP_NO
        , (CASE 
            WHEN AVG(SCORE) >= 96 THEN 'S'
            WHEN AVG(SCORE) >= 90 THEN 'A'
            WHEN AVG(SCORE) >= 80 THEN 'B'
        ELSE 'C' END) AS GRADE
    FROM HR_GRADE
    GROUP BY EMP_NO
)

SELECT a.EMP_NO
     , a.EMP_NAME
     , b.GRADE
     , (CASE 
        WHEN b.GRADE = 'S' THEN a.SAL * 0.2
        WHEN b.GRADE = 'A' THEN a.SAL * 0.15
        WHEN b.GRADE = 'B' THEN a.SAL * 0.1
        ELSE a.sal * 0 END) AS BONUS
FROM HR_EMPLOYEES a
INNER JOIN GRADE_TABLE b
ON a.EMP_NO = b.EMP_NO
ORDER BY a.EMP_NO ASC

아이디어

• 2022년 사원의 평가 반기/분기 평균 평가점수와 등급을 매긴 가상 테이블 생성
• HR_EMPLOYEES와 INNER JOIN을 한 뒤, 등급에 따라 성과금 계산

HR_DEPARTMENT 테이블은 필요없네요!

[icegosimperson]

문제: 그룹별 조건에 맞는 식당 목록 출력하기

설명

리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문

코드

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(b.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE m
JOIN REST_REVIEW b
ON m.MEMBER_ID = b.MEMBER_ID
WHERE b.MEMBER_ID = ( 
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
ORDER BY b.REVIEW_DATE ASC, b.REVIEW_TEXT;

아이디어

• 서브쿼리로 리뷰 가장 많이 작성한 회원 필터링 : 내림차순 정렬 + LIMIT 1
• DATE_FORMAT : '%Y-%m-%d' 날짜 형식 맞춰 출력

[baexxbin]

문제: 177. Nth Highest Salary

설명

• Employee 테이블에서 n번째로 큰 급여를 반환하는 query를 작성
• 만약에 n번째로 큰 급여 정보가 없다면, NULL을 반환

코드

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      # Employee 테이블에서 n번째로 큰 급여를 반환하는 query를 작성
      # 만약에 n번째로 큰 급여 정보가 없다면, NULL을 반환
      SELECT DISTINCT salary
      FROM Employee
      ORDER BY salary DESC
      LIMIT 1 OFFSET N
  );
END

아이디어

• 제공된 함수 구조

   CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT    // 함수 이름 정의 및 매개변수, 반환값 설정
   BEGIN
     -- 함수 내부 로직
   END

• 변수 값 설정
  • SET N = N-1;
    • 0베이스 인덱스로 사용하기 위해 1빼기
• 값 반환
  • RETURN () 안에 SELECT문을 통해 값 반환
• 쿼리 동작
  • 내림차순 정렬 후 N번째 값 구하기
  • LIMIT 1 OFFSET N
    • LIMIT 1: 하나의 결과만 가져옴
    • OFFSET N: 결과의 시작 위치를 N으로 설정\
  • DISTINCT로 중복된 급여 제거

오호 함수를 공부해볼 수 있었어요

[jewan100]

문제: 3374. First Letter Capitalization II

설명

• 이건 mysql로는 풀 수 없는거 정론...
• 지영님 저번주에 고생하셨습니다... 세상에 이걸 어떻게..

코드

SELECT content_id, content_text AS original_text,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT(UPPER(SUBSTRING(content_text, 1, 1)), LOWER(SUBSTRING(content_text, 2, LENGTH(content_text) - 1))), " z", " Z"), " y", " Y"), " x", " X"), " w", " W"), " v", " V"), " u", " U"), " t", " T"), " s", " S"), " r", " R"), " q", " Q"), " p", " P"), " o", " O"), " n", " N"), " m", " M"), " l", " L"), " k", " K"), " j", " J"), " i", " I"), " h", " H"), " g", " G"), " f", " F"), " e", " E"), " d", " D"), " c", " C"), " b", " B"), " a", " A") ,"-z", "-Z"),"-y", "-Y"),"-x", "-X"),"-w", "-W"),"-v", "-V"),"-u", "-U"),"-t", "-T"),"-s", "-S"),"-r", "-R"),"-q", "-Q"),"-p", "-P"),"-o", "-O"),"-n", "-N"),"-m", "-M"),"-l", "-L"),"-k", "-K"),"-j", "-J"),"-i", "-I"),"-h", "-H"),"-g", "-G"),"-f", "-F"),"-e", "-E"),"-d", "-D"),"-c", "-C"),"-b", "-B"),"-a", "-A") AS converted_text FROM user_content

아이디어

• 조건에 맞게 치환