프로그래머스 SQL - JOIN

1

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

SELECT A.FLAVOR
FROM FIRST_HALF A JOIN JULY B
ON A.FLAVOR = B.FLAVOR
GROUP BY A.FLAVOR
ORDER BY SUM(A.TOTAL_ORDER) DESC
LIMIT 3

2

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

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

3

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

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

4

없어진 기록 찾기

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS AS O
LEFT OUTER JOIN ANIMAL_INS AS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID, O.NAME

5

있었는데요 없었습니다

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS AS I
JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.DATETIME > O.DATETIME
ORDER BY I.DATETIME ASC

6

오랜 기간 보호한 동물(1)

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS AS I
LEFT JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.DATETIME IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3

7

보호소에서 중성화한 동물

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 <> O.SEX_UPON_OUTCOME

8

상품 별 오프라인 매출 구하기

SELECT PRODUCT.PRODUCT_CODE, 
(PRODUCT.PRICE * SUM(OFFLINE_SALE.SALES_AMOUNT)) AS SALES
FROM PRODUCT
INNER JOIN OFFLINE_SALE
ON PRODUCT.PRODUCT_ID = OFFLINE_SALE.PRODUCT_ID
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

9

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

SELECT   
    YEAR(S.SALES_DATE)AS YEAR
    ,MONTH(S.SALES_DATE)AS MONTH
    ,(COUNT(DISTINCT(S.USER_ID))) AS PUCHASED_USERS
    ,ROUND(((COUNT(DISTINCT(S.USER_ID)))/(SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED)=2021)),1)AS PUCHASED_RATIO
FROM ONLINE_SALE AS S
LEFT JOIN USER_INFO AS U 
ON S.USER_ID = U.USER_ID
WHERE YEAR(U.JOINED) = 2021 
GROUP BY YEAR,MONTH
ORDER BY YEAR,MONTH