🔓 06. 프로그래머스 LEVEL4, 5 문제풀이

모바일은 화면을 돌려 가로화면으로 보시는 게 읽으시기 편할 수 있습니다. 돌려서 보시는 걸 추천드릴게요!!

 

 

 

원래 문항은 구문에 따라 구분되어 있지만, 알고 푸는 것보다 모르고 푸는 게 재밌을 것 같아 난이도별로 공부하고 있습니다 :)

 

---

🔑 Q38. 보호소에서 중성화한 동물       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS AS I
INNER JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.SEX_UPON_INTAKE LIKE 'Intact%'
AND (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY 1;

 

---

⛔ Q39. 입양 시각 구하기(2)       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- WITH구문 : 임시 테이블 생성 구문

- 재귀함수 구문 : RECURSIVE 사용

-- 재귀함수 : 0~23 까지의 시간 변수 생성
WITH RECURSIVE TEMP AS 
(
    SELECT 0 AS TIME
    UNION ALL 
    SELECT TIME + 1 FROM TEMP WHERE TIME < 23
)

SELECT TIME AS HOUR, COUNT(DISTINCT(ANIMAL_ID)) AS COUNT
FROM TEMP AS T
LEFT JOIN ANIMAL_OUTS AS O
ON T.TIME = HOUR(O.DATETIME)
GROUP BY 1

 

 

☑️ RECURSIVE 구문 정리

WITH RECURSIVE 테이블명 AS 
(
    SELECT 초기값 AS 열 이름
    UNION ALL
    SELECT (열 이름 계산식) FROM 테이블명 WHERE 열 이름 ~~ 조건
)

SELECT * FROM 테이블명

 

---

🔑 Q40. 우유와 요거트가 담긴 장바구니        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- 두 상품을 모두 구매한 고객 / 장바구니 찾기 문제 : SELF JOIN 사용. 구매한 상품의 모든 카테고리 결합 케이스 쿼리

SELECT DISTINCT(C1.CART_ID)
FROM CART_PRODUCTS AS C1
INNER JOIN CART_PRODUCTS AS C2
ON C1.CART_ID = C2.CART_ID
WHERE C1.NAME = 'Milk' AND C2.NAME = 'Yogurt'
ORDER BY 1;

 

 

---

🔑 Q41. 식품분류별 가장 비싼 식품의 정보 조회하기        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- 품목별 최고가의 제품 조회 : GROUP BY를 사용하는 경우, 품목별 최고가 상품명이 실제 데이터와 다른 경우가 발생함

- 따라서, 서브쿼리를 사용해 품목별 최고가 추출 후에 제품명을 출력하는 게 더욱 안정적인 쿼리임 

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN 
(
    SELECT CATEGORY, MAX(PRICE)
    FROM FOOD_PRODUCT
    GROUP BY 1
    HAVING CATEGORY IN ('과자', '국', '김치', '식용유')
)
ORDER BY 2 DESC;

 

---

🔑 Q42. 5월 식품들의 총매출 조회하기      

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, sum(PRICE * AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P
INNER JOIN FOOD_ORDER AS O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE PRODUCE_DATE LIKE '2022-05-%'
GROUP BY 1, 2
ORDER BY 3 DESC, 1

 

---

🔑 Q43. 서울에 위치한 식당 목록 출력하기       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, sum(PRICE * AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P
INNER JOIN FOOD_ORDER AS O
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE PRODUCE_DATE LIKE '2022-05-%'
GROUP BY 1, 2
ORDER BY 3 DESC, 1

 

---

⛔ Q44. 그룹별 조건에 맞는 식당 목록 출력하기        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이 1 - WITH 구문을 사용한 임시 테이블 작성 쿼리

-- ID 별 작성 리뷰 개수 추출 테이블
WITH TEMP1 AS 
(
    SELECT MEMBER_ID, COUNT(REVIEW_TEXT) AS COUNT
    FROM REST_REVIEW
    GROUP BY 1
    ORDER BY 2 DESC  
),

-- 제일 많은 수의 리뷰를 작성한 ID 추출 테이블
TEMP2 AS 
(
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY 1
    HAVING COUNT(REVIEW_TEXT) = (SELECT MAX(COUNT) FROM TEMP1)
)

-- 목록 출력 쿼리
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS P
INNER JOIN REST_REVIEW AS R
ON P.MEMBER_ID = R.MEMBER_ID
WHERE P.MEMBER_ID IN (SELECT MEMBER_ID FROM TEMP2)
ORDER BY 3, 2;

 

 

☑️ 풀이 2 - RANK 함수 사용 쿼리

- RANK( ) 함수 : RANK( ) OVER(ORDER BY 열 이름 정렬방식) AS RANKING

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM 
(
    SELECT P.MEMBER_ID, MEMBER_NAME, COUNT(REVIEW_TEXT) AS REVIEW_NUM,
           RANK() OVER(ORDER BY COUNT(REVIEW_TEXT) DESC) AS RANKING
    FROM MEMBER_PROFILE AS P
    INNER JOIN REST_REVIEW AS R
    ON P.MEMBER_ID = R.MEMBER_ID
    GROUP BY 1
) AS TEMP

INNER JOIN REST_REVIEW AS R 
ON TEMP.MEMBER_ID = R.MEMBER_ID
WHERE RANKING = 1
ORDER BY 3 ,2

 

---

⛔ Q45. 년, 월, 성별 별 구매 회원 수 구하기        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- 회원 수 or 주문 수 주의해서 구할 것 

- 문제 잘 읽고 중복 처리 여부 잘 판단할 것

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, 
       GENDER, COUNT(DISTINCT(S.USER_ID)) AS USERS
FROM USER_INFO AS I
INNER JOIN ONLINE_SALE AS S
ON I.USER_ID = S.USER_ID AND I.GENDER IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

 

---

🔑 Q46. 오프라인/온라인 판매 데이터 통합하기      

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE 
WHERE SALES_DATE LIKE '2022-03-%'

UNION ALL

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE SALES_DATE LIKE '2022-03-%'

ORDER BY 1, 2, 3;

 

---

🔑 Q47. 취소되지 않은 진료 예약 조회하기       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

 

- 중첩 조인 

SELECT APNT_NO, PT_NAME, P.PT_NO, MCDP_CD, DR_NAME, APNT_YMD 
FROM 
(
    SELECT APNT_NO, PT_NO, A.MCDP_CD, DR_NAME, APNT_YMD
    FROM DOCTOR AS D
    INNER JOIN APPOINTMENT AS A
    ON D.DR_ID = A.MDDR_ID AND APNT_YMD LIKE '2022-04-13%'
    WHERE A.MCDP_CD = 'CS' AND A.APNT_CNCL_YN = 'N'
) AS TEMP
INNER JOIN PATIENT AS P
ON P.PT_NO = TEMP.PT_NO
ORDER BY 6

 

---

🔑 Q48. 주문량이 많은 아이스크림들 조회하기       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT J.FLAVOR
FROM JULY AS J
LEFT JOIN FIRST_HALF AS H
ON J.SHIPMENT_ID = H.SHIPMENT_ID
GROUP BY 1
ORDER BY SUM(IFNULL(H.TOTAL_ORDER, 0) + J.TOTAL_ORDER) DESC LIMIT 3;

 

---

🔑 Q49. 저자 별 카테고리 별 매출액 집계하기       

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(PRICE * SALES) AS TOTAL_SALES 
FROM
(
    SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, BOOK_ID, PRICE
    FROM BOOK AS B
    INNER JOIN AUTHOR AS A
    ON B.AUTHOR_ID = A.AUTHOR_ID
) AS TEMP
INNER JOIN BOOK_SALES AS S
ON TEMP.BOOK_ID = S.BOOK_ID
WHERE S.SALES_DATE LIKE '2022-01-%'
GROUP BY 1, 3
ORDER BY 1, 3 DESC;

 

---

🔑 Q50. 자동차 대여 기록 별 대여 금액 구하기        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- CASE WHEN THEN ~  WHEN THEN ~  ELSE  END 구문

- DATEDIFF 함수를 사용해서 기간을 구할 때, +1을 해줘야 올바른 기간을 구할 수 있음

SELECT HISTORY_ID, FLOOR((DAILY_FEE - (DAILY_FEE * IFNULL(DISCOUNT_RATE, 0) / 100)) * DURATION) AS FEE 
FROM
(
    SELECT HISTORY_ID, DAILY_FEE, DATEDIFF(END_DATE, START_DATE) + 1 AS DURATION,
    CASE WHEN (DATEDIFF(END_DATE, START_DATE) + 1) < 7 THEN NULL
    WHEN (DATEDIFF(END_DATE, START_DATE) + 1) < 30 THEN '7일 이상'
    WHEN (DATEDIFF(END_DATE, START_DATE) + 1) < 90 THEN '30일 이상'
    ELSE '90일 이상' END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR AS C
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    ON C.CAR_ID = H.CAR_ID AND C.CAR_TYPE = '트럭'  
) AS TEMP
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
ON TEMP.DURATION_TYPE = P.DURATION_TYPE AND P.CAR_TYPE = '트럭'
ORDER BY 2 DESC, 1 DESC

 

---

⛔ Q51. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기        

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- 대여 가능한 기간을 산출할 때 사용한 쿼리가 틀려서 고민했던 문제

- 서브쿼리 사용할 때 해당 쿼리의 결과 잘 살펴보고 사용하기

SELECT CAR_ID, TEMP.CAR_TYPE, FLOOR((DAILY_FEE - (DAILY_FEE * DISCOUNT_RATE / 100)) * 30) AS FEE
FROM
(
    SELECT DISTINCT(H.CAR_ID), DAILY_FEE, C.CAR_TYPE, '30일 이상' AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR AS C
    INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    ON C.CAR_ID = H.CAR_ID AND C.CAR_TYPE IN ('세단', 'SUV')
    GROUP BY 1
    HAVING MAX(END_DATE) < '2022-11-01' OR MAX(START_DATE) > '2022-11-30'
) AS TEMP
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
ON TEMP.CAR_TYPE = P.CAR_TYPE AND TEMP.DURATION_TYPE = P.DURATION_TYPE
WHERE FLOOR((DAILY_FEE - (DAILY_FEE * DISCOUNT_RATE / 100)) * 30) BETWEEN 500000 AND 2000000
ORDER BY 3 DESC, 2, 1 DESC;

 

---

⛔ Q52. 상품을 구매한 회원 비율 구하기      

 

☑️ 문제

프로그래머스

 

 

☑️ 풀이

- SELECT 문 내에서 SELECT 문 사용해서 서브쿼리 느낌으로 사용하기

- SELECT NULL AS 별칭 : 열을 NULL로 채워줌

- SELECT '~~~' AS 별칭 : 열을 문자열 '~~~' 으로 채워줌

- SELECT (SELECT 열이름 FROM 테이블) AS 별칭 : 열을 테이블의 데이터로 채워줌

-- 2021년에 가입한 회원 수 쿼리 테이블
WITH TEMP AS 
(
    SELECT COUNT(USER_ID) AS AA FROM USER_INFO WHERE JOINED LIKE '2021-%'
)

-- SELECT 문 내에서 SELECT 문 사용해서 TEMP 테이블의 데이터 추출
SELECT YEAR(SALES_DATE), MONTH(SALES_DATE), COUNT(DISTINCT(USER_ID)) AS PUCHASED_USERS, 
ROUND((COUNT(DISTINCT(USER_ID)) / (SELECT AA FROM TEMP)), 1) AS PURCHASED_RATIO
FROM ONLINE_SALE  
WHERE USER_ID IN
(
    SELECT USER_ID FROM USER_INFO WHERE JOINED LIKE '2021-%'
)
GROUP BY 1, 2
ORDER BY 1, 2;

 

---

 

총 3개의 포스팅을 통해서, 프로그래머스의 MySQL 문제 중 LEVEL 2~5까지의 문제를 모두 풀어보았습니다. 풀던 도중 헷갈리는 문제들도 있었고, 시간이 많이 소모된 문제들도 있었지만 재밌다는 느낌이 더 많이 들었던 것 같습니다. 정리해둔 문제들은 한번씩 더 풀어보고, 더 깔끔한 쿼리를 짤 수 있도록 더 많이 공부해야겠다는 생각도 들었습니다ㅎㅎ .아직 MySQL에서 제공하는 다양한 기능들을 사용하지는 못했지만, 앞으로 다양한 문제를 해결하면서 하나하나 쌓아가겠습니다!! 

 

---