[11주차_SQL] SQL 풀이 모음

[yeongleej]

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

• 고다혜: 그룹별 조건에 맞는 식당 목록 출력하기 Second Highest Salary

• 배수빈: 저자 별 카테고리 별 매출액 집계하기 언어별 개발자 분류하기

• 백제완: Trips and Users Students and Examinations

• 이예진: 자동차 대여 기록 별 대여 금액 구하기 Investments in 2016

• 이지영: 멸종위기의 대장균 찾기 조건에 맞는 개발자 찾기

• 이혜원: Department Top Three Salaries Human Traffic of Stadium

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

### 설명

### 코드

### 아이디어

[yeahdy]

문제: 자동차 대여 기록 별 대여 금액 구하기

설명

• 자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE) 을 구하여 대여 기록 ID와 대여 금액 리스트를 출력
• CAR_RENTAL_COMPANY_CAR : 대여 중인 자동차들의 정보 (cc)
• CAR_RENTAL_COMPANY_RENTAL_HISTORY : 자동차 대여 기록 정보 (ch)
• CAR_RENTAL_COMPANY_DISCOUNT_PLAN : 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 (cp)

코드

SELECT 
    ch.HISTORY_ID,
    IF(DISCOUNT_RATE IS NULL, 
       daily_fee * FEE, 
       FLOOR((daily_fee * FEE)*(1-(DISCOUNT_RATE/100)))) FEE
FROM CAR_RENTAL_COMPANY_CAR cc
INNER JOIN (
    SELECT HISTORY_ID, CAR_ID, TIMESTAMPDIFF(DAY,START_DATE,END_DATE)+1 FEE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) ch
ON cc.CAR_ID = ch.CAR_ID
LEFT JOIN (
    SELECT SUBSTRING_INDEX(DURATION_TYPE,'일',1) DAY,
            SUBSTRING_INDEX(DISCOUNT_RATE,'%',1) DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE CAR_TYPE = '트럭'
) cp
ON (CASE 
        WHEN ch.FEE BETWEEN 7 AND 30 THEN 7
        WHEN ch.FEE BETWEEN 30 AND 90 THEN 30
        WHEN ch.FEE >= 90 THEN 90
    END) = cp.DAY
WHERE cc.CAR_TYPE = '트럭'
ORDER BY FEE DESC, HISTORY_ID DESC;

아이디어

• INNER JOIN 자동차 대여 기록 정보 조인
  • 대여 기간에 따른 할인 조건이 다르기 때문에 날짜 차이 계산 + 할인 연산 시 칼럼을 재사용하기 위해 조인
  • TIMESTAMPDIFF(단위, 날짜1, 날짜2) 단위: SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
• LEFT JOIN 할인 정책 정보 조인
  • 대여 일수가 7일 미만일 경우 할인 정책에 포함되지 않기 때문에 모든 대여일수를 조회하기 위해 LEFT JOIN 사용
  • 일수 별로 적용되는 할인 정책이 다르므로 할인 일수(DAY)와 할인율(DISCOUNT_RATE) 문자열 분리
  • SUBSTRING_INDEX(문자열,구분자,인덱스)
  • CASE 문 조건을 통한 조인 여러 대여 기간 일수들이 할인 정책에 정확하게 포함하기 위해 CASE 문을 통해 단일화하여 조인

[baexxbin]

문제: 저자 별 카테고리 별 매출액 집계하기

설명

• BOOK, AUTHOR, BOOK_SALES 테이블 존재
• 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력

코드

SELECT b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY,
        SUM(s.SALES*b.PRICE) AS TOTAL_SALES
FROM BOOK b
JOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
JOIN BOOK_SALES s ON b.BOOK_ID = s.BOOK_ID
WHERE DATE_FORMAT(s.SALES_DATE, '%Y-%m') = '2022-01'
GROUP BY b.AUTHOR_ID, a.AUTHOR_NAME, b.CATEGORY
ORDER BY b.AUTHOR_ID, b.CATEGORY DESC;

아이디어

• 테이블 3개 조인
  • BOOK, AUTHOR, BOOK_SALES 3개의 정보가 다 필요하기에 3개 조인 걸기
• 조건에 따른 날짜 정보 맞추기

  이때 날짜는 DATE_FORMAT말고도 LIKE, YEAR, MONTH등을 이용할 수 있음

• 조건에 따라 저자와 카테고리 별로 그룹화 및 정렬 진행
• TOTAL_SALES 구하기
  • 판매량을 구해야하기에, 각 그룹별 팔린 책의 총합에 대한 책 값을 곱해줌 (SUM)
  • 이때 SUM 위치 유의하기
    • BOOK_ID별 매출액이 아닌 (저자ID, 카테고리)별 매출액을 계산 함
    • 한 작가가 (저자ID, 카테고리)가 같은 서로 다른 책을 쓸 수 있음
    • SUM(SALES*PRICE)가 아닌 SUM(SALES)*PRICE형태로 작성할 경우, PRICE값은 각 컬럼 별 알맞은 값이 아닌, 임의 대표로 한가지 값이 계산되므로 안됨!

[yeongleej]

문제: 멸종위기의 대장균 찾기

설명

• 각 세대별 자식이 없는 개체수(COUNT)와 세대(GENERATION)을 세대에 대해 오름차순으로 출력

=> 각 개체의 세대를 찾아야 함 => 자식이 없는 개체를 찾아야 함

코드

WITH RECURSIVE GEN_DATA AS(
    SELECT ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT E.ID, G.GENERATION+1
    FROM GEN_DATA G
    JOIN ECOLI_DATA E
    ON G.ID = E.PARENT_ID
)

SELECT COUNT(P.ID) AS COUNT, G.GENERATION
FROM ECOLI_DATA P
JOIN GEN_DATA G
ON P.ID = G.ID
LEFT JOIN ECOLI_DATA C
ON P.ID = C.PARENT_ID
WHERE C.ID IS NULL
GROUP BY G.GENERATION
ORDER BY G.GENERATION

아이디어

• 각 개체의 세대 찾기 => 재귀 가상테이블 활용 WITH RECURSIVE cte AS()

  
  -- RECURSIVE CTE
  WITH RECURSIVE cte[가상테이블 명]  AS 
  (
  SELECT ...   -- Non Recursive 부분
  UNION ALL 
  SELECT ...  -- Recursive 부분
  )
  SELECT * FROM cte;

- 부모가 없는 세대를 1세대로 시작으로 다음 세대를 차례대로 구하기
![image](https://github.com/user-attachments/assets/08590b88-967f-4fdc-9f5d-498867b3f69c)

- 자식이 없는 개체 찾기
    - **SELF JOIN**으로 자식이 없는 개체 찾기
    - `p.id` : 부모 개체의 ID
    - `c.parent_id` : 자식의 부모 ID
    - 부모 테이블 기준으로 left join하면 자식이 없는 부모의 자식 id 값은 null이 됨
![image](https://github.com/user-attachments/assets/620cb06e-4e5c-4160-a922-763f8c4f1e87)

[icegosimperson]

문제: Department Top Three Salaries

설명

각 부서 고소득 직원 찾기 고소득 기준 : 해당 부서의 상위 3개 고유 급여에 포함

코드

SELECT t.department, t.employee, t.salary
FROM (
         SELECT d.name AS department,
                e.name AS employee,
                e.salary AS Salary,
                DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr
         FROM employee e
                  JOIN Department d ON e.departmentId=d.id
     ) t
WHERE t.dr<=3;

아이디어

부셔별 상위 급여를 먼저 순위 매기고 조건 추가

1) 부셔별 상위 급여 : DENSE_RANK() 2) PARTITION BY로 부서별 데이터 나누기

DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS dr

• GROUP BY와 다르게 데이터 유지한 상태로 그룹 계산 수행 가능

3) 인라인 뷰로 순위를 한번만 계산

SELECT column_list
FROM (SELECT * FROM table_name) [alias]
WHERE 조건식;

• 순위 계산 중복 방지

[KodaHye]

문제: Second Highest Salary

설명

• id와 급여에 대한 정보가 있는 Employee 테이블이 있을 때, 급여가 두 번째로 큰 사람의 급여 정보 출력하기 (없으면 null 출력)

코드

SELECT MAX(SALARY) AS `SecondHighestSalary`
FROM EMPLOYEE
WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEE)

아이디어

• MAX를 통해 가장 큰 급여를 찾고, 그것보다 작은 것중에서 가장 큰(두 번째로 큰) 급여를 찾음

혜원님이 저번에 알려주신 OFFSET, LIMIT을 사용하는게 제일 깔끔한 풀이 같습니다. 근데 해커랭크에서 아무 값도 나오지 않아야 될 때, null이라는 글자를 따로 출력해야되는건가요??? 코드가 틀린건가요?!?