[MySQL] 프로그래머스 Lv.4 Lv.5

• [MySQL] 프로그래머스 LV.4 LV.5

---

문제

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

코딩테스트 연습 - 우유와 요거트가 담긴 장바구니 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) = 2
ORDER BY CART_ID;

• 특정 구매자가 우유와 요거트를 구매한 로그가 있어야한다.
• 우유와 요거트를 구매한 로그만 뽑는다.
• GROUP BY를 사용해서 특정 구매자 별로 묶는다.
• 중복을 제거하고 row 개수가 2개이면 둘 다 구매한 사람들의 로그.
• distinct를 사용하지 않으면, 요거트만 2개 구매한 사람이 잡히고 이 count가 2개 이상일 수 있다.

 

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

코딩테스트 연습 - 식품분류별 가장 비싼 식품의 정보 조회하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM (
    SELECT *, RANK() OVER (PARTITION BY CATEGORY ORDER BY PRICE DESC) AS PRICERANK
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
) AS PRICERANKING
WHERE PRICERANK = 1
ORDER BY MAX_PRICE DESC;

• RANK 함수를 사용해서 카테고리 별 순위를 매긴 테이블을 만든다.
• 서브쿼리를 사용해서 PRICERANK가 1인 값들을 모두 가져온다.
• 내림차순으로 출력하기.

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

코딩테스트 연습 - 보호소에서 중성화한 동물 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT I.ANIMAL_ID, I.ANIMAL_TYPE, I.NAME
FROM ANIMAL_INS AS I
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 I.ANIMAL_ID;

• 들어올 때에는 중성화 INTACT 조건을 먼저 걸어주고, 나갈 때에는 중성화된 조건을 걸어준다.
• ID는 들어올 때, 나갈 때 동일하니 JOIN 컬럼으로 사용.

 

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

코딩테스트 연습 - 5월 식품들의 총매출 조회하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT FP.PRODUCT_ID, FP.PRODUCT_NAME, SUM(FO.AMOUNT)*FP.PRICE AS TOTAL_SALES
FROM FOOD_PRODUCT FP
JOIN FOOD_ORDER FO ON FP.PRODUCT_ID = FO.PRODUCT_ID
WHERE YEAR(FO.PRODUCE_DATE) = 2022 AND MONTH(FO.PRODUCE_DATE) = 5
GROUP BY FP.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, FP.PRODUCT_ID;

• PRODUCT_ID 컬럼을 중심으로 조인을 진행한다.
• 조인한 테이블에서 날짜 조건을 걸어준다.
• FOOD_ORDER 테이블에서 같은 상품이어도 주문별로 다르게 처리가 되니 GROUP BY로 묶어준다.
• GROUP BY로 묶고 AMOUNT를 모두 더한 값에 PRICE를 곱해서 총 매출을 구한다.
• 가격에 대해서 내림차순, ID에 대해서 오름차순

5. 서울에 위치한 식당 출력하기

코딩테스트 연습 - 서울에 위치한 식당 목록 출력하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT RI.REST_ID, RI.REST_NAME, RI.FOOD_TYPE, RI.FAVORITES, RI.ADDRESS, ROUND(AVG(RR.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO AS RI
JOIN REST_REVIEW AS RR ON RI.REST_ID = RR.REST_ID
WHERE RI.ADDRESS LIKE '서울%'
GROUP BY RI.REST_ID
ORDER BY SCORE DESC, RI.FAVORITES DESC;

• 주소에 서울시가 아닌데 서울이 포함된 주소가 있다고 합니다...... 맞왜틀?
• GROUP BY + 집계함수 정도만 주의하면 된다.
• 처음에는 SUBSTR로 서울을 추출하려고 했는데 이상한 주소가 있을까봐 LIKE '%서울%'로 바꿨는데 앞에 서울을 떼야 하는듯

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

코딩테스트 연습 - 오프라인/온라인 판매 데이터 통합하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3

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 YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 3

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

• JOIN을 써야되냐, UNION을 써야되냐 고민을 했다.
• 기존 판매 데이터에 판매 유형에 ON/OFF로 구분되어있다고 하면 데이터를 수평 구분한 것.
• 수평 구분된 데이터를 단순히 합쳐주기만 하면 된다.
• 통합 과정에서 오프라인 데이터에는 값이 없으니 NULL AS로 채워준다.

 

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

코딩테스트 연습 - 주문량이 많은 아이스크림들 조회하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT FLAVOR
FROM (
    SELECT FLAVOR, TOTAL_ORDER
    FROM FIRST_HALF
    UNION ALL
    SELECT FLAVOR, TOTAL_ORDER
    FROM JULY
) AS UNTIL_JULY
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3;

• 6번과 마찬가지로 SALES 기간에만 차이가 있다.
• 기간에 따라 수평분할한 두 테이블을 다시 UNION으로 합친다.
• 서브쿼리로 합친 테이블을 넣어주고, GROUP BY로 묶고 주문량 합으로 정렬기준 걸어주기.

 

8. 그룹별 조건에 맞는 식당 출력하기

코딩테스트 연습 - 그룹별 조건에 맞는 식당 목록 출력하기 | 프로그래머스 스쿨 (programmers.co.kr)

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

• GROUP BY로 묶은 후 COUNT(*)로 리뷰를 가장 많이 쓴 사람을 찾는다.
• 정렬한 후 맨 위에 있는 사람을 찾아도 되고, MAX를 통해서 가장 큰 값의 리뷰어가 누군지 찾아도 된다.
• 서브쿼리를 이용해서 얻은 사람의 이름이랑 같은 사람의 리뷰 목록들을 출력해준다.

9. 년, 월, 성별 별 상품 구매 회원 수 구하기

코딩테스트 연습 - 년, 월, 성별 별 상품 구매 회원 수 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

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

• WHERE절에서 성별이 빈칸으로 돼있으면 가져오지 않는다.
• GROUP BY에 다중 COLUMNS을 사용한다.
• 동일한 날짜 회원 상품 조합에 대해서는 하나의 판매 데이터만 존재!
• 그룹 별 통계를 낼 때, 동일 날짜여도 월별 통계를 내야한다.
• 같은 구매자가 GROUP BY로 여러 데이터로 묶일 수 있어서 DISTINCT 사용

 

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

코딩테스트 연습 - 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

SELECT C.CAR_ID, C.CAR_TYPE, FLOOR(C.DAILY_FEE*30*(1-P.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR AS C
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P ON C.CAR_TYPE = P.CAR_TYPE   
WHERE
    C.CAR_TYPE IN ('세단','SUV') AND
    (FLOOR(C.DAILY_FEE*30*(1-P.DISCOUNT_RATE/100)) BETWEEN 500000 AND 1999999) AND
    P.DURATION_TYPE = '30일 이상' AND
    C.CAR_ID NOT IN
            (
            SELECT CAR_ID 
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
            WHERE (START_DATE LIKE '2022-11%' OR END_DATE LIKE '2022-11%') OR
                (START_DATE <= '2022-11-01' AND END_DATE >= '2022-11-30')
            )
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC;

• 가장 까다로운게 날짜 처리.
• 불가능한 CAR_ID를 서브쿼리로 만들어서 CAR_ID가 해당 쿼리에 들어있지 않은거로 조건을 걸어준다.
• 나머지 부분들은 크게 메인 쿼리의 조건으로 걸어준다.
• 정수 버림으로  FLOOR나 TRUNCATE를 사용해주면 된다. 정수라서 BEETWEEN 190만으로 걸어주기.

 

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

코딩테스트 연습 - 상품을 구매한 회원 비율 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

WITH J AS
    (SELECT *
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021)

SELECT 
    YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH,
    COUNT(DISTINCT USER_ID) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT USER_ID)/(SELECT COUNT(*) FROM J),1) AS PUCHASED_RATIO
FROM ONLINE_SALE 
WHERE USER_ID IN (SELECT USER_ID FROM J)
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

• 위에 문제처럼 서브쿼리를 안에 넣으니 쿼리가 너무 난잡해져서 WITH 사용.
• 전체 테이블에서 2021년에 한 번 이라도 구매한 사람들의 비율을 구한다.
• GROUP BY로 묶었을 때 같은 년,월에 있으면 중복으로 카운트 되므로 DISTINCT를 사용해준다.

 

12. 입양 시각 구하기(2)

코딩테스트 연습 - 입양 시각 구하기(2) | 프로그래머스 스쿨 (programmers.co.kr)

WITH TEMP AS (
  SELECT 0 AS HOUR
  UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
  SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
  SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
  SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
  SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
  SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23
)
SELECT TEMP.HOUR, COALESCE(COUNT(ANIMAL_OUTS.ANIMAL_ID), 0) AS COUNT
FROM TEMP
LEFT JOIN ANIMAL_OUTS ON TEMP.HOUR = HOUR(ANIMAL_OUTS.DATETIME)
GROUP BY TEMP.HOUR
ORDER BY TEMP.HOUR;


with recursive time as (
    select 0 as hour
    union all
    select hour+1 from time where hour<23)

SELECT r.hour as HOUR,COUNT(ANIMAL_ID)as COUNT
FROM ANIMAL_OUTS as o right join time as r
on HOUR(o.DATETIME) = r.hour
GROUP BY 1
ORDER BY 1

• 왜 레벨 높은가 했더니 재귀로 테이블을 먼저 채워주는 문제였음.... 몰라서 그냥 짰는데
• FOR문 처럼 조건 맞으면 계속 루프 돌려주는 시스템인듯

 

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

코딩테스트 연습 - 취소되지 않은 진료 예약 조회하기 | 프로그래머스 스쿨 (programmers.co.kr)

WITH CANCLE AS
    (
    SELECT *
    FROM APPOINTMENT
    WHERE MCDP_CD = 'CS' AND APNT_CNCL_YN = 'N'
    )

SELECT C.APNT_NO, P.PT_NAME, C.PT_NO, C.MCDP_CD, D.DR_NAME, C.APNT_YMD
FROM CANCLE AS C
JOIN PATIENT AS P ON P.PT_NO = C.PT_NO
JOIN DOCTOR AS D ON D.DR_ID = C.MDDR_ID
WHERE C.APNT_YMD LIKE '%2022-04-13%'
ORDER BY 6

 

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

코딩테스트 연습 - 저자 별 카테고리 별 매출액 집계하기 | 프로그래머스 스쿨 (programmers.co.kr)

WITH COND AS (
    SELECT *
    FROM BOOK_SALES
    WHERE YEAR(SALES_DATE) = 2022 AND MONTH(SALES_DATE) = 1
)

SELECT A.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, SUM(B.PRICE * S.SALES) AS TOTAL_SALES
FROM COND AS S
JOIN BOOK AS B ON S.BOOK_ID = B.BOOK_ID
JOIN AUTHOR AS A ON A.AUTHOR_ID = B.AUTHOR_ID
GROUP BY A.AUTHOR_ID, B.CATEGORY
ORDER BY A.AUTHOR_ID ASC, B.CATEGORY DESC;

• COND로 팔린 기간에 맞는 데이터들만 모아놓는다.
• JOIN으로 모아놓고 매출액 집계할 때만 각 테이블에 맞는 가격이랑 매출량 곱해주기.

 

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

코딩테스트 연습 - 그룹별 조건에 맞는 식당 목록 출력하기 | 프로그래머스 스쿨 (programmers.co.kr)

WITH MAX_REVIEW AS (
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY COUNT(*) DESC
LIMIT 1)

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW AS R
JOIN MEMBER_PROFILE AS M ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (SELECT MEMBER_ID FROM MAX_REVIEW)
ORDER BY 3,2

• 문제가 좀 이상해서 삽질했다. 문제 이름도 이상함
• 가장 리뷰를 많이 쓴 사람이 여러 명인 경우를 취급하지 않는 것 같은데 이 부분은 체크 해줘야할 듯.
• 정답 제출한 코드에서는 가장 많이 제출한 사람의 리뷰를 출력하는 코드가 정답
• WITH로 가장 많이 리뷰를 쓴 사람을 찾는다.
• JOIN을 시킨 후, MEMBER ID가 WITH 구문에 리뷰를 가장 많이 쓴 사람과 일치하면 통과

16. FrontEnd 개발자 찾기

코딩테스트 연습 - FrontEnd 개발자 찾기 | 프로그래머스 스쿨 (programmers.co.kr)

# SOL1)
SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS AS D
WHERE EXISTS (
    SELECT 1
    FROM SKILLCODES AS S
    WHERE D.SKILL_CODE & S.CODE AND S.CATEGORY = 'Front End'
)
ORDER BY D.ID;

# SOL2)
SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS D
JOIN SKILLCODES S ON D.SKILL_CODE & S.CODE != 0
WHERE S.CATEGORY = 'Front End'
GROUP BY D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
ORDER BY D.ID;

• 비트 연산을 통해서 스킬 int와 개발자 int 비교하기.
• 스킬 int의 특정 비트를 제외하고는 모두 0이니, AND 연산을 하면 해당 스킬의 유무를 알 수 있다.
• 채찍피티한테 물어보니까 아래 코드 처럼 JOIN절에 조건 넣어서 쓰는 풀이도 가능한듯.
• 보통같으면 JOIN을 한 후에 WHERE를 걸어줘야 하는데, JOIN 전 부터 비교가 필요한 쿼리에서는 JOIN절에 조건 포함시키기