7.sql

-- [ 프로그래머스 ] 서울에 위치한 식당 목록 출력하기

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


-- 아래와 같이 서브쿼리로 푸는 게 더 정확한 정답이다.

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