(Programmers) SQL 풀이 (6)

✏️ Programmers MySQL 정답률 73~74% 전체 풀이

1️⃣ 3월에 태어난 여성 회원 목록 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131120)

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH, "%Y-%m-%d")
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
AND GENDER = "W"
AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;

2️⃣ 즐겨찾기가 가장 많은 식당 정보 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131123)

# 방법 (1) : RANK() OVER (PARTITION BY ~) 사용
SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
FROM (
    SELECT *, RANK() OVER (PARTITION BY RI.FOOD_TYPE ORDER BY RI.FAVORITES DESC) AS RRI
    FROM REST_INFO AS RI
) AS RANKING
WHERE RANKING.RRI <= 1
ORDER BY FOOD_TYPE DESC;

# 방법 (2) : SUB Table 사용
SELECT RI.FOOD_TYPE, RI.REST_ID, RI.REST_NAME, RI.FAVORITES
FROM REST_INFO AS RI
INNER JOIN (
    SELECT FOOD_TYPE, MAX(FAVORITES) AS MAXLITE
    FROM REST_INFO
    GROUP BY FOOD_TYPE
) AS RM
ON RI.FOOD_TYPE = RM.FOOD_TYPE AND RI.FAVORITES = RM.MAXLITE
ORDER BY FOOD_TYPE DESC;