[MySQL] 프로그래머스 SQL 고득점 Kit -JOIN 11 문제 풀이

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

너무 어렵다.. 익숙해지자 

큰 조건들을 미리 정리해두고 JOIN!

대여금액 할인율 적용 & 시작날과 종료날을 고려해서 not in으로 서브쿼리 두기

• 일일 대여금액에 discount_rate을 적용하고 30일을 곱해준다 → 정수이므로 round로 나타내기
• 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능해야 하므로 대여가 끝나는 날이 2022-11-01을 넘고 대여가 시작하는 날이 2022-11-30 전인 경우를 모두 제외해야한다

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(((C.DAILY_FEE) * (100-P.DISCOUNT_RATE))/100 *30) AS FEE

FROM CAR_RENTAL_COMPANY_CAR C JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H 
    ON C.CAR_ID = H.CAR_ID
    JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE
    
WHERE C.CAR_TYPE IN ('세단','SUV') 
    AND P.DURATION_TYPE = '30일 이상'
    AND C.CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE END_DATE >= '2022-11-01' AND START_DATE < '2022-12-01')
    

GROUP BY C.CAR_ID
HAVING FEE  >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

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

7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다. → FLAVOR로 GROUP BY 해야한단 뜻! shipment id 가 아닌 맛으로 조회하는 것임

SELECT F.FLAVOR
FROM FIRST_HALF F JOIN JULY J
ON F.FLAVOR = J.FLAVOR
GROUP BY FLAVOR
ORDER BY SUM(F.TOTAL_ORDER + J.TOTAL_ORDER) DESC
LIMIT 3

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

SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%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 ASC

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

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

SUM 붙이는 것 잊지 말 것

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

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

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

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R
    ON M.MEMBER_ID = R.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, REVIEW_TEXT

WHERE = 로 서브쿼리로 가장 많이 작성한 회원 찾기!!

없어진 기록 찾기 Lv3

입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요. → ANIMAL OUTS에는 있는데 AMINAL INS에는 없는 동물 아이디와 이름을 조회!

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN (
    SELECT ANIMAL_ID
    FROM ANIMAL_INS
)

간단한 문제!

있었는데요 없었습니다 Lv3

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순 → ASC! 으로 조회해야합니다.

-- 코드를 입력하세요
SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

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

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

-- 코드를 입력하세요
SELECT NAME, DATETIME
FROM ANIMAL_INS 
WHERE ANIMAL_ID NOT IN (
    SELECT ANIMAL_ID
    FROM ANIMAL_OUTS)
ORDER BY DATETIME 
LIMIT 3

보호소에서 중성화한 동물 Lv4 - With as 여러개! 서브쿼리 개념!!

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

서브쿼리 사용법 빼고는 어렵지 않았다!

WITH tmp AS (
    SELECT *
    FROM ANIMAL_INS
    WHERE SEX_UPON_INTAKE LIKE 'Intact%'
), tmp2 AS (
    SELECT *
    FROM ANIMAL_OUTS
    WHERE SEX_UPON_OUTCOME LIKE 'Spayed%' OR SEX_UPON_OUTCOME LIKE 'Neutered%'
)

SELECT t.ANIMAL_ID, t.ANIMAL_TYPE, t.NAME
FROM tmp t JOIN tmp2 t2 on t.ANIMAL_ID = t2.ANIMAL_ID

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

PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

→ PRODUCT_ID로 조인한다! product 테이블의 price와 offline_sale테이블의 sales_amount 계산하기

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

상품을 구매한 회원 비율 구하기 Lv5 ! 굉장히 어려움

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

1. 2021년에 가입한 전체 회원들 중 상품을 구매!!한 회원수
2. 상품을 구매한 회원의 비율 (=2021년에 가입한 회원 중 상품 구입한 회원 / 2021에 가입한 전체 회원 수)
3. → 소수점 두번째 자리에서 반올림 → Round(계산,1) 첫째자리까지만 나타내기

한 사람이 상품을 여러번 구매할 수 있으니 이는 dIstinct로 중복 제거한다

SELECT year(sales_date) as year, month(sales_date) as month, count(distinct user_id) as puchased_users,
round(count(distinct user_id)/(select count(*) from user_info where year(joined)=2021),1) as puchased_ratio

from online_sale

where user_id in (
    select user_id
    from user_info
    where year(joined) = 2021)

group by year(sales_date), month(sales_date)
order by year, month

round ((online sale의 중복배제한 user id의 count / user_info에서 2021년에 가입한 count전부) , 1)

년, 월 별로 → group by year, month!!