프로그래머스 코딩테스트 연습 Lv4

프로그래머스 MySQL Lv4

---

FOOD_PRODUCT 테이블
: PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미

 

식품분류별 가장 비싼 식품의 정보 조회하기
: 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

 

 

풀이 1

- WHERE절 서브쿼리 사용

- 같은 테이블 간 비교이므로 테이블 별칭 사용해주기 

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT A
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
AND PRICE = (SELECT MAX(PRICE)
              FROM FOOD_PRODUCT B
              WHERE A.CATEGORY = B.CATEGORY)
ORDER BY 2 DESC

 

풀이 2

- WHERE절 서브쿼리 사용

  다중열 서브쿼리 : (열1, 열2, ...) 괄호로 묶어서 IN 함수 사용 

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

 

풀이 3

- FROM절 서브쿼리(인라인 뷰) 사용 

SELECT CATEGORY, MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT A INNER JOIN (SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
                                FROM FOOD_PRODUCT
                                GROUP BY CATEGORY) B USING(CATEGORY)
WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
AND A.PRICE = B.MAX_PRICE
ORDER BY 2 DESC

 

 

 

 

 

CART_PRODUCTS 테이블
: ID, CART_ID, NAME, PRICE는 각각 테이블의 아이디, 장바구니의 아이디, 상품 종류, 가격을 나타냄

우유와 요거트가 담긴 장바구니
: 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

풀이 1

- set 연산자 INTERSECT 사용

- 각각의 SELECT 문은 괄호로 묶기

  + 개별 SELECT 문에는 ORDER BY절 사용 불가, ORDER BY절은 최종 결과에서만 사용 가능

(SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME ='Milk')
INTERSECT
(SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME = 'Yogurt')
ORDER BY 1

 

풀이 2

- ID를 기준으로 GROUP BY → HAVING으로 필터링 

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

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID
HAVING MIN(NAME) = 'Milk' AND MAX(NAME) = 'Yogurt'
ORDER BY CART_ID;

 

 

 

 

 

 

FIRST_HALF 테이블
: SHIPMENT_ID, FLAVOR, TOTAL_ORDER는 각각 아이스크림 공장에서 아이스크림 가게까지의 출하 번호, 아이스크림 맛, 상반기 아이스크림 총주문량을 나타냄. FIRST_HALF 테이블의 기본 키는 FLAVOR이며, FIRST_HALF테이블의 SHIPMENT_ID는 JULY테이블의 SHIPMENT_ID의 외래 키

JULY 테이블
: FIRST_HALF 테이블과 동일한 스키마. 7월 아이스크림 총주문량을 나타냄. JULY 테이블의 기본 키는 SHIPMENT_ID이며 JULY테이블의 FLAVOR는 FIRST_HALF 테이블의 FLAVOR의 외래 키

주문량이 많은 아이스크림들 조회하기
: 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

 

 

풀이 1

- JOIN 사용, JOIN 전에 각 테이블에서 TOTAL_ORDER을 합하거나 JOIN 이후 HAVING에서 합치기

- ORDER BY + LIMIT로 상위 record n개 출력 

SELECT FLAVOR
FROM FIRST_HALF F LEFT OUTER JOIN JULY J USING(FLAVOR)
GROUP BY FLAVOR
ORDER BY SUM(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER) DESC
LIMIT 3

SELECT FLAVOR
FROM (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
      FROM FIRST_HALF
      GROUP BY FLAVOR) F INNER JOIN 
      (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
      FROM JULY
      GROUP BY FLAVOR) J USING(FLAVOR)
GROUP BY FLAVOR
ORDER BY SUM(F.TOTAL_ORDER) + SUM(J.TOTAL_ORDER) DESC
LIMIT 3

 

풀이 2

- set 연사자 UNION 사용, FIRST_HALF와 JULY를 합친 테이블을 새로운 테이블로 명명

  이때 UNION 되는 두 테이블의 스키마는 동일해야 함

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

 

 

 

 

 

MEMBER_PROFILE 테이블
: MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미

REST_REVIEW 테이블
: REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미

그룹별 조건에 맞는 식당 목록 출력하기
: 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

 

풀이 1

- WHERE절 서브쿼리 이용

- 최댓값이 여러 record일 수 있으므로 IN 함수 사용 

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW A INNER JOIN MEMBER_PROFILE M USING(MEMBER_ID)
WHERE MEMBER_ID IN (SELECT MEMBER_ID
                   FROM REST_REVIEW
                   GROUP BY MEMBER_ID
                   HAVING COUNT(*) = (SELECT COUNT(*)
                                      FROM REST_REVIEW
                                      GROUP BY MEMBER_ID
                                      ORDER BY COUNT(*) DESC
                                      LIMIT 1)
                    )                   
ORDER BY 3, 2

 

풀이 2

- WINDOW 함수 RANK 이용

- 최댓값이 여러 record인 경우 모든 record에 동일한 순위값을 부여해야하므로

  ROW_NUMBER는 사용하기 적절하지 않음 → RANK, DENSE_RANK 등 이용 

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW A INNER JOIN (
    SELECT MEMBER_NAME, MEMBER_ID, RANKING
    FROM MEMBER_PROFILE M INNER JOIN (
        SELECT MEMBER_ID, RANK() OVER (ORDER BY COUNT(*) DESC) AS RANKING
        FROM REST_REVIEW GROUP BY MEMBER_ID) B USING(MEMBER_ID)
    ) C USING(MEMBER_ID)
WHERE RANKING = 1
ORDER BY 3, 2