[프로그래머스 SQL 고득점 Kit] JOIN

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

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

문제부터 너무 길다. 하나씩 조건들을 분리하면 아래와 같다.

-- 자동차 종류가 '세단' 또는 'SUV' 인 자동차
-- 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
-- 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서
-- [SELECT] 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력
-- [ORDER] 결과는 대여 금액 내림차순, 자동차 종류 오름차순, 자동차 ID 내림차순 정렬

 

CAR_RENTAL_COMPANY_CAR에서 자동차 종류 선택

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022-11-01~2022-11-30 사이에 대여 기록이 있는 자동차 제외

일일 대여 금액 DAILY_FEE*30 한 다음 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류 CAR_TYPE와 대여 기간 DURATION_TYPE이 같은 할인정책의 할인율 DISCOUNT_RATE을 선택해서 금액에 적용

 

2022년 11월 1일부터 2022년 11월 30일 사이에 대여 불가능한 자동차 목록 구하기

 

 

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 자동차 목록

SELECT CAR_ID
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_ID NOT IN
(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE<'2022-11-01' &&  '2022-11-30'<END_DATE
) AND CAR_TYPE='세단' || CAR_TYPE='SUV'

 

이제 이 중에서 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차를 고르면 된다.

-- 자동차 종류가 '세단' 또는 'SUV' 인 자동차
-- 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
-- 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서
-- [SELECT] 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력
-- [ORDER] 결과는 대여 금액 내림차순, 자동차 종류 오름차순, 자동차 ID 내림차순 정렬
SELECT CAR_ID, c.CAR_TYPE, ROUND(DAILY_FEE*30*((100-DISCOUNT_RATE)/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR c
LEFT JOIN (
    SELECT CAR_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE DURATION_TYPE ='30일 이상'
) d ON c.CAR_TYPE = d.CAR_TYPE
WHERE CAR_ID NOT IN
(
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE START_DATE<'2022-11-01' &&  '2022-11-30'<END_DATE
) AND c.CAR_TYPE='세단' || c.CAR_TYPE='SUV'
AND FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, c.CAR_TYPE, CAR_ID DESC

 

WITH CALCULATED_FEES AS (
    SELECT c.CAR_ID, c.CAR_TYPE,
        ROUND(c.DAILY_FEE * ((100 - COALESCE(d.DISCOUNT_RATE, 0)) / 100) * 30) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR c
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d 
        ON c.CAR_TYPE = d.CAR_TYPE
        AND d.DURATION_TYPE = '30일 이상'
)

SELECT CAR_ID, CAR_TYPE, FEE
FROM CALCULATED_FEES
WHERE CAR_ID NOT IN (
    SELECT CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE '2022-11-01'<=START_DATE AND '2022-11-30'<=END_DATE
)
AND CAR_TYPE IN ('세단', 'SUV')
AND FEE BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

=> 틀린 이유: 하나의 차에는 11월에 대여한 기록과 11월이 아닌 달에 대여한 기록이 있다. 11월 대여 기록이 있는 모든 차를 제거하면 안 된다.

https://school.programmers.co.kr/questions/84219

SELECT C.CAR_ID, C.CAR_TYPE, ROUND(C.DAILY_FEE*30*(1-P.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR C
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE
	AND P.DURATION_TYPE = '30일 이상'
WHERE C.CAR_TYPE IN ('SUV', '세단')
GROUP BY C.CAR_ID, C.CAR_TYPE
HAVING MIN(H.START_DATE) > '2022-11-30' OR MAX(H.END_DATE) < '2022-11-01'
AND FEE BETWEEN 500000 AND 2000000

 

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

-- FOOD_PRODUCT와 FOOD_ORDER 테이블
-- 생산일자가 2022년 5월인 식품들
-- 식품 ID, 식품 이름, 총매출을 조회
-- 결과는 총매출 내림차순 정렬, 식품 ID 오름차순 정렬
SELECT p.PRODUCT_ID, p.PRODUCT_NAME, SUM(PRICE*AMOUNT)AS TOTAL_SALES 
FROM FOOD_PRODUCT p
JOIN FOOD_ORDER o
ON p.PRODUCT_ID=o.PRODUCT_ID
WHERE PRODUCE_DATE LIKE'2022-05%'
GROUP BY o.PRODUCT_ID
ORDER BY TOTAL_SALES DESC , p.PRODUCT_ID ASC

 

보호소에서 중성화한 동물

# 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물
    # 중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact,
    # 중성화를 거친 동물은 Spayed 또는 Neutered
# 아이디, 생물 종, 이름을 조회
# 아이디 순으로 조회
SELECT i.ANIMAL_ID, i.ANIMAL_TYPE, i.NAME
FROM ANIMAL_INS i
JOIN ANIMAL_OUTS o USING(ANIMAL_ID)
WHERE i.SEX_UPON_INTAKE LIKE 'Intact%'
AND (o.SEX_UPON_OUTCOME LIKE 'Spayed%' OR o.SEX_UPON_OUTCOME LIKE'Neutered%')

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

# PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력
# 결과는 매출액 내림차순, 상품코드 오름차순
SELECT PRODUCT_CODE, SUM(PRICE*SALES_AMOUNT) AS SALES
FROM OFFLINE_SALE off
JOIN PRODUCT p USING (PRODUCT_ID)
GROUP BY PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

 

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

-- USER_INFO 테이블과 ONLINE_SALE 테이블
# 2021년에 가입한 전체 회원들 중
# 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력
# 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림
# 결과는 년을 기준으로 오름차순, 월을 기준으로 오름차순 정렬
WITH USER_COUNT AS(
    SELECT COUNT(*) AS COUNT
    FROM USER_INFO
    WHERE YEAR(JOINED)='2021'
)

SELECT YEAR(b.SALES_DATE) AS YEAR,
MONTH(b.SALES_DATE) AS MONTH,
COUNT(DISTINCT(B.USER_ID)) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT(B.USER_ID))/c.COUNT, 1) AS PUCHASED_RATIO
FROM USER_INFO a, ONLINE_SALE b, USER_COUNT c
WHERE a.USER_ID = b.USER_ID
AND YEAR(a.JOINED) = '2021'
GROUP BY YEAR(b.SALES_DATE), MONTH(b.SALES_DATE)
ORDER BY YEAR(b.SALES_DATE), MONTH(b.SALES_DATE)

 

FrontEnd 개발자 찾기

-- DEVELOPERS 테이블
# Front End 스킬을 가진 개발자의 정보(개발자의 ID, 이메일, 이름, 성) 조회
# 결과는 ID를 기준으로 오름차순 정렬
SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d, (SELECT CODE
                    FROM SKILLCODES
                    WHERE CATEGORY='Front End') s
WHERE d.SKILL_CODE&s.CODE = s.CODE
ORDER BY d.ID ASC