[프로그래머스 SQL] 그룹별 조건에 맞는 식당 목록 출력하기

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

다음은 고객의 정보를 담은 MEMBER_PROFILE테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다. MEMBER_PROFILE 테이블은 다음과 같으며 MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.

 

Column name	Type	Nullable
MEMBER_ID	VARCHAR(100)	FALSE
MEMBER_NAME	VARCHAR(50)	FALSE
TLNO	VARCHAR(50)	TRUE
GENDER	VARCHAR(1)	TRUE
DATE_OF_BIRTH	DATE	TRUE

 

REST_REVIEW 테이블은 다음과 같으며 REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.

Column name	Type	Nullable
REVIEW_ID	VARCHAR(10)	FALSE
REST_ID	VARCHAR(10)	TRUE
MEMBER_ID	VARCHAR(100)	TRUE
REVIEW_SCORE	NUMBER	TRUE
REVIEW_TEXT	VARCHAR(1000)	TRUE
REVIEW_DATE	DATE	TRUE

---

문제

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

---

예시

MEMBER_PROFILE 테이블이 다음과 같고

MEMBER_ID	MEMBER_NAME	TLNO	GENDER	DATE_OF_BIRTH
jiho92@naver.com	이지호	01076432111	W	1992-02-12
jiyoon22@hotmail.com	김지윤	01032324117	W	1992-02-22
jihoon93@hanmail.net	김지훈	01023258688	M	1993-02-23
seoyeons@naver.com	박서연	01076482209	W	1993-03-16
yelin1130@gmail.com	조예린	01017626711	W	1990-11-30

 

REST_REVIEW 테이블이 다음과 같을 때

REVIEW_ID	REST_ID	MEMBER_ID	REVIEW_SCORE	REVIEW_TEXT	REVIEW_DATE
R000000065	00028	soobin97@naver.com	5	부찌 국물에서 샤브샤브 맛이나고 깔끔	2022-04-12
R000000066	00039	yelin1130@gmail.com	5	김치찌개 최곱니다.	2022-02-12
R000000067	00028	yelin1130@gmail.com	5	햄이 많아서 좋아요	2022-02-22
R000000068	00035	ksyi0316@gmail.com	5	숙성회가 끝내줍니다.	2022-02-15
R000000069	00035	yoonsy95@naver.com	4	비린내가 전혀없어요.	2022-04-16

 

SQL을 실행하면 다음과 같이 출력되어야 합니다.

MEMBER_NAME	REVIEW_TEXT	REVIEW_DATE
조예린	김치찌개 최곱니다.	2022-02-12
조예린	햄이 많아서 좋아요	2022-02-22

---

주의사항

REVIEW_DATE의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.

 

 

내가 쓴 코드)

WITH BEST_MEMBER 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 MEMBER_PROFILE AS M JOIN REST_REVIEW AS R ON M.MEMBER_ID=R.MEMBER_ID
JOIN BEST_MEMBER AS B ON M.MEMBER_ID = B.MEMBER_ID
ORDER BY R.REVIEW_DATE ASC, R.REVIEW_TEXT ASC
;

 

→ 그냥 아예 리뷰 가장 많이 작성한 회원을 먼저 꼽아서 테이블로 만들어줬음.

→ 그리고 나머지 두 테이블이랑 다 JOIN 해서 원하는거 SELECT 해주기.

 

다른 방법들)

1) 순위를 매기는 테이블을 만들어놓고, 나중에 1등을 뽑기

WITH c1 AS(
    SELECT member_id, DENSE_RANK() OVER(ORDER BY COUNT(member_id) DESC) ranking
    FROM REST_REVIEW 
    GROUP BY member_id
)
SELECT member_name, review_text, DATE_FORMAT(review_date, '%Y-%m-%d') review_date # SUBSTR(review_date, 1, 10)
FROM MEMBER_PROFILE m 
JOIN c1 ON m.member_id = c1.member_id
JOIN REST_REVIEW r ON m.member_id = r.member_id
WHERE ranking = 1
ORDER BY review_date, review_text;

 

✔ RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환한다. ex)1,2,2,4
✔ DENSE_RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환한다. ex)1,2,2,3
✔ ROW_NUMBER : 중복 관계없이 순차적으로 순위를 반환한다. ex)1,2,3,4

 

 

2) WITH 구문 안쓰고 WHERE 절에 서브쿼리 써서 해결하기

SELECT p.member_name 멤버명, r.review_text 리뷰, DATE_FORMAT(r.review_date, '%Y-%m-%d') 리뷰작성일
FROM member_profile p, rest_review r
WHERE p.member_id = r.member_id
      AND p.member_id = (SELECT r.member_id
                        FROM rest_review r
                        GROUP BY r.member_id
                        ORDER BY COUNT(*) DESC
                        LIMIT 1)
ORDER BY 리뷰작성일, 리뷰;

---

+ 추가로 서브쿼리 쓰는 비슷한 문제

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

다음은 식품의 정보를 담은 FOOD_PRODUCT 테이블입니다. FOOD_PRODUCT 테이블은 다음과 같으며 PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE는 식품 ID, 식품 이름, 식품코드, 식품분류, 식품 가격을 의미합니다.

문제

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

 

예시

FOOD_PRODUCT 테이블이 다음과 같을 때,

PRODUCT_ID	PRODUCT_NAME	PRODUCT_CD	CATEGORY	PRICE
P0018	맛있는고추기름	CD_OL00008	식용유	6100
P0019	맛있는카놀라유	CD_OL00009	식용유	5100
P0020	맛있는산초유	CD_OL00010	식용유	6500
P0021	맛있는케첩	CD_SC00001	소스	4500
P0022	맛있는마요네즈	CD_SC00002	소스	4700
P0039	맛있는황도	CD_CN00008	캔	4100
P0040	맛있는명이나물	CD_CN00009	캔	3500
P0041	맛있는보리차	CD_TE00010	차	3400
P0042	맛있는메밀차	CD_TE00001	차	3500
P0099	맛있는맛동산	CD_CK00002	과자	1800

 

SQL을 실행하면 다음과 같이 출력되어야 합니다.

CATEGORY	MAX_PRICE	PRODUCT_NAME
식용유	6500	맛있는산초유
과자	1800	맛있는맛동산

 

답)

SELECT CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
    SELECT CATEGORY, MAX(PRICE) as PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY)
ORDER BY PRICE desc

 

**이게 안되는 이유는 뭘까?

SELECT CATEGORY,PRICE AS MAX_PRICE,PRODUCT_NAME
from FOOD_PRODUCT
where price in (select max(PRICE)
            from FOOD_PRODUCT
            WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
            GROUP BY CATEGORY )

GROUP BY CATEGORY
ORDER BY MAX_PRICE DESC;

 

⇒ in() 안의 쿼리를 실행할 경우 전체 카테고리에서 최대 가격만 출력됨.

우리가 알고 싶은 것은 각 카테고리 별 최대 가격임.