[SQL] 프로그래머스 SQL문제풀이, JOIN

Donghun Seol·2023년 3월 18일
0

SQL

목록 보기
2/2

LEVEL 2

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

DATE_FORMAT 함수, % format specifier, JOIN

-- 경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME)
-- 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성해주세요.
-- 결과는 출판일을 기준으로 오름차순 정렬해주세요.

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

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

JOIN, GROUP BY, SUM(), multi-column ORDER

COUNT를 사용하는게 아니라 SUM()을 사용해야 했다.
그래야 판매량을 고려한 총 매출액을 구할 수 있다.

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

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

LEVEL 3

레벨3 문제는 전부다 동물데이터 관련이라 별도의 포스팅에 정리했다.

LEVEL 4

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

서브쿼리, cte를 활용하는 풀이도 있던데 추후에 살펴볼것.

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

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

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

-- 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 
-- 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.
-- JULY 테이블에 중복된 FLAVOR 튜플이 있으므로 GROUP BY로 묶어줘야 한다.

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

JOIN을 사용하지 않은 풀이

SELECT JU.FLAVOR
  FROM JULY JU, FIRST_HALF FI
  WHERE JU.FLAVOR = FI.FLAVOR
  GROUP BY JU.FLAVOR
  ORDER BY SUM(JU.TOTAL_ORDER) + SUM(FI.TOTAL_ORDER) DESC
  LIMIT 3

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

IN ('val1', 'val2'), STR_TO_DATE(), 3 tables 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를 기준으로 내림차순 정렬해주세요.
*/
-- 0.9라는 할인율을 변수로 가져와야되는데 이걸 모르겠다. CASE로 해결해야되나?


SELECT c.CAR_ID, c.CAR_TYPE, FLOOR(c.DAILY_FEE * 30 * 0.9) AS FEE
FROM CAR_RENTAL_COMPANY_CAR c
INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.CAR_ID = h.CAR_ID
INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON c.CAR_TYPE = d.CAR_TYPE
WHERE c.CAR_TYPE IN ('세단', 'SUV') AND
c.DAILY_FEE * 30 * 0.9 >= 500000 AND
c.DAILY_FEE * 30 * 0.9 < 2000000 AND
h.START_DATE < STR_TO_DATE('2022-11-30', '%Y-%m-%d') AND
h.END_DATE < STR_TO_DATE('2022-11-01', '%Y-%m-%d')
ORDER BY FEE DESC, c.CAR_TYPE, c.CAR_ID DESC

답안을 봤는데 한번에 이해하기 힘들어서
또 다시 GPT형님께 조언을 구했다.
>아래 sql 쿼리를 코드에 직접 주석을 달면서 한국어로 최대한 상세하게 설명해줘

-- 메인 쿼리 시작
SELECT
    A.CAR_ID,          -- 차량 아이디
    A.CAR_TYPE,        -- 차량 유형 (세단 또는 SUV)
    A.FEE              -- 할인된 월간 비용
FROM (
    -- 서브쿼리 시작
    SELECT 
        C.CAR_ID,      -- 차량 아이디
        C.CAR_TYPE,    -- 차량 유형 (세단 또는 SUV)
        C.DAILY_FEE,   -- 차량의 일일 요금
        ROUND((C.DAILY_FEE * (100-D.DISCOUNT_RATE)/100) * 30) FEE,  -- 할인 적용된 월간 비용
        D.DISCOUNT_RATE,   -- 할인율
        D.DURATION_TYPE,   -- 할인 요금제의 기간 유형
        H.START_DATE,      -- 대여 시작 날짜
        H.END_DATE         -- 대여 종료 날짜
    FROM CAR_RENTAL_COMPANY_CAR C
        -- 렌탈 기록과 차량 테이블 왼쪽 조인
        LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
        ON C.CAR_ID=H.CAR_ID
        -- 할인 플랜과 차량 테이블 조인
        JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
        ON C.CAR_TYPE=D.CAR_TYPE
    WHERE C.CAR_TYPE IN ('세단', 'SUV')  -- 세단과 SUV 차량만 대상
        AND D.DURATION_TYPE IN (30)      -- 30일 할인 요금제만 대상
    GROUP BY
        C.CAR_ID, D.DISCOUNT_RATE, D.DURATION_TYPE
) A
WHERE
    A.FEE BETWEEN 500000 AND 2000000     -- 월간 비용이 500,000원에서 2,000,000원 사이인 경우만 대상
    AND (DATEDIFF(A.END_DATE,'2022-11-01') < 0
    OR DATEDIFF(A.START_DATE,'2022-11-30') > 0)  -- 2022년 11월에 대여 가능한 차량만 대상
ORDER BY
    A.FEE DESC, A.CAR_TYPE ASC, A.CAR_ID DESC  -- 결과를 월간 비용 내림차순, 차량 유형 오름차순, 차량 아이디 내림차순으로 정렬

이 쿼리는 다음 단계로 진행됩니다.

  1. 서브쿼리에서 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블을 조인하여 세단과 SUV 차량의 할인 정보를 가져옵니다. 또한 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블을 왼쪽 조인하여 대여 시작 날짜와 종료 날짜 정보를 가져옵니다.

  2. 서브쿼리의 WHERE 절에서 차량 유형이 '세단' 또는 'SUV'이며, 할인 요금제의 기간 유형이 30일인 경우만 필터링합니다.

  3. 서브쿼리에서 차량의 일일 요금과 할인율을 이용해 할인된 월간 비용을 계산하고, GROUP BY 절로 그룹화합니다.

  4. 메인 쿼리에서 서브쿼리의 결과를 이용해 월간 비용이 500,000원에서 2,000,000원 사이인 경우만 필터링하고, 2022년 11월에 대여 가능한 차량만 필터링합니다.

  5. ORDER BY 절을 사용하여 결과를 월간 비용 내림차순, 차량 유형 오름차순, 차량 아이디 내림차순으로 정렬합니다.

  6. 쿼리의 결과로, 자동차 렌탈 회사에서 할인 요금제를 적용한 세단 및 SUV 차량의 월간 비용과 관련된 정보가 반환됩니다. 이 정보는 고객이 예산에 맞는 차량을 선택할 수 있도록 도움을 줍니다.

단돈 월 20달러에 언제든지 질문에 답변해주는 과외선생님이라니 너무너무 좋다.

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

--  GROUP BY와 SUM() 집계함수 사용하기, 
-- 적절하게 컬럼명 만들기, MONTH()함수 사용하기,

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

LEVEL 5

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

profile
I'm going from failure to failure without losing enthusiasm

0개의 댓글