[SQL 고득점 Kit] JOIN 풀이

프로그래머스 SQL 고득점 Kit JOIN 12문항에 대한 풀이입니다. MySQL 문법 기준으로 작성했습니다! 🔥

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

SELECT F.FLAVOR
FROM FIRST_HALF F
LEFT JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER_JULY
    FROM JULY
    GROUP BY FLAVOR
) J ON F.FLAVOR = J.FLAVOR
ORDER BY (F.TOTAL_ORDER + COALESCE(J.TOTAL_ORDER_JULY, 0)) DESC
LIMIT 3;

 

💡 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

SELECT 
    C.CAR_ID, 
    C.CAR_TYPE,
    FLOOR(C.DAILY_FEE * (100 - COALESCE(P.DISCOUNT_RATE, 0)) * 0.01 * 30) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE AND P.DURATION_TYPE = '30일 이상'
WHERE C.CAR_ID NOT IN (
    SELECT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
)
AND C.CAR_TYPE IN ('세단', 'SUV')
AND FLOOR(C.DAILY_FEE * (100 - COALESCE(P.DISCOUNT_RATE, 0)) * 0.01 * 30) BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, C.CAR_TYPE, C.CAR_ID DESC;

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(O.AMOUNT) * P.PRICE TOTAL_SALES
FROM FOOD_ORDER O
LEFT JOIN FOOD_PRODUCT P
    ON O.PRODUCT_ID = P.PRODUCT_ID
WHERE DATE_FORMAT(O.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC;

 

💡 조건에 맞는 도서와 저자 리스트 출력하기

https://school.programmers.co.kr/learn/courses/30/lessons/144854

프로그래머스

SELECT 
    B.BOOK_ID AS BOOK_ID, 
    A.AUTHOR_NAME AS AUTHOR_NAME, 
    DATE_FORMAT(B.PUBLISHED_DATE, '20%y-%m-%d') AS PUBLISHED_DATE
FROM 
    BOOK B
JOIN AUTHOR A 
    ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE 
    B.CATEGORY = '경제'
ORDER BY 
    B.PUBLISHED_DATE;

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

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

 

💡 없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

프로그래머스

SELECT 
    AOUT.ANIMAL_ID AS ANIMAL_ID, 
    AOUT.NAME AS NAME
FROM 
    ANIMAL_INS AIN
RIGHT JOIN ANIMAL_OUTS AOUT
    ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE 
    AIN.ANIMAL_ID IS NULL;

 

💡 있었는데요 없었습니다

https://school.programmers.co.kr/learn/courses/30/lessons/59043

프로그래머스

SELECT 
    AIN.ANIMAL_ID AS ANIMAL_ID, 
    AIN.NAME AS NAME
FROM 
    ANIMAL_INS AIN
JOIN ANIMAL_OUTS AOUT
    ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE 
    AIN.DATETIME > AOUT.DATETIME
ORDER BY 
    AIN.DATETIME;

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/59044

프로그래머스

SELECT 
    AIN.NAME AS NAME, 
    AIN.DATETIME AS DATETIME
FROM 
    ANIMAL_INS AIN
LEFT JOIN ANIMAL_OUTS AOUT
    ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE 
    AOUT.ANIMAL_ID IS NULL
ORDER BY 
    AIN.DATETIME
LIMIT 3;

 

💡 보호소에서 중성화한 동물

https://school.programmers.co.kr/learn/courses/30/lessons/59045

프로그래머스

SELECT 
    AIN.ANIMAL_ID AS ANIMAL_ID, 
    AIN.ANIMAL_TYPE AS ANIMAL_TYPE, 
    AIN.NAME AS NAME
FROM 
    ANIMAL_INS AIN
JOIN ANIMAL_OUTS AOUT
    ON AIN.ANIMAL_ID = AOUT.ANIMAL_ID
WHERE 
    (AIN.SEX_UPON_INTAKE LIKE 'Intact%' AND AOUT.SEX_UPON_OUTCOME LIKE 'Spayed%') 
    OR 
    (AIN.SEX_UPON_INTAKE LIKE 'Intact%' AND AOUT.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY 
    AIN.ANIMAL_ID;

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131533

프로그래머스

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

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131534

프로그래머스

SELECT 
    YEAR(OS.SALES_DATE) AS YEAR, 
    MONTH(OS.SALES_DATE) AS MONTH, 
    COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT OS.USER_ID) / (
        SELECT COUNT(*) FROM USER_INFO UI WHERE YEAR(UI.JOINED) = '2021'
    ), 1) AS PURCHASED_RATIO
FROM 
    USER_INFO UI
RIGHT JOIN ONLINE_SALE OS
    ON UI.USER_ID = OS.USER_ID
WHERE 
    YEAR(UI.JOINED) = '2021'
GROUP BY 
    YEAR, MONTH
ORDER BY 
    YEAR, MONTH;

 

💡 FrontEnd 개발자 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/276035

프로그래머스

SELECT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME
FROM DEVELOPERS D
WHERE EXISTS (
    SELECT 1
    FROM SKILLCODES S
    WHERE S.CATEGORY = 'Front End'
    AND (D.SKILL_CODE & S.CODE) = S.CODE
)
ORDER BY D.ID;