[문제]
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.


[단계별 풀이]
1. 우선 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회인 자동차들을 필터링 하기 위해 서브쿼리 작성
2. 해당 서브쿼리에 속하면서 다시 START_DATE가 8~10월 사이인 CAR_ID만 필터링

3. MONTH와 CAR_ID를 GROUP BY

4. HISTORY_ID를 COUNT

5. MONTH를 오름차순 정렬, CAR_ID를 내림차순 정렬

[정답]

SELECT MONTH(START_DATE) AS MONTH,
       CAR_ID,
       COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE CAR_ID IN (SELECT CAR_ID
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                 GROUP BY CAR_ID
                 HAVING COUNT(HISTORY_ID) >= 5)
AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY 1, 2
ORDER BY MONTH, CAR_ID DESC

[문제]
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.


[단계별 풀이]
1. 2022년 1월인 행들만 필터링

2. 저자ID, 저자명, 카테고리 별로 GROUP BY 

3. 매출액(판매량 * 판매가)를 SUM 

4. 저자 ID 오름차순 정렬, 카테고리 내림순 정렬

[정답]

SELECT A.AUTHOR_ID,
       A.AUTHOR_NAME,
       B.CATEGORY,
       SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM BOOK_SALES BS
JOIN BOOK B ON BS.BOOK_ID = B.BOOK_ID
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE BS.SALES_DATE LIKE '2022-01%'
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC

필터링을 해주고 난 뒤 JOIN 하면 성능 향상을 기대해볼 수 있다

SELECT A.AUTHOR_ID,
       A.AUTHOR_NAME,
       B.CATEGORY,
       SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM (
    SELECT *
    FROM BOOK_SALES
    WHERE SALES_DATE LIKE '2022-01%'
) BS
JOIN BOOK B ON BS.BOOK_ID = B.BOOK_ID
JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC

[문제]
https://school.programmers.co.kr/learn/courses/30/lessons/133027

[단계별 풀이] (틀림) 
1.두 테이블을 FLAVOR 기준으로 JOIN

2. FLAVOR 별 주문량 합계를 구한 뒤 내림차순 정렬

3. 3개 행만 출


[정답] (사실 오답임. 아래 이유)

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

FIRST_HALF 테이블의 strawberry 가 중복된 모습을 볼 수 있다.

JULY 테이블은 FLAVOR가 기본키가 아니라서 두개의 strawberry가 존재하기 때문.

그래서 두 행의 TOTAL_ORDER 를 더하면 각각 3620 과 3320이 되어버린다.

이 후 GROUP BY를 통해 총 판매량을 구하면 6940이 되어서 판매량이 뻥튀기된다.

테스트 케이스가 적어서 그런 듯 하다

별 생각없이 조인하면서 풀다가 어 맞네 하고 넘어간듯

해당 문제는 테이블을 조인하는 것이 아닌 위아래로 붙이면 해결된다.

테이블을 위아래로 붙여준 뒤에 맛 별로 그룹화해서 총 판매량을 구하고 정렬한 뒤 3개 행만 출력하자. 

[정답] 

SELECT FLAVOR
FROM
(
    SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
    UNION ALL
    SELECT FLAVOR, TOTAL_ORDER FROM JULY
) sub
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

[문제]
USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.


[단계별 풀이]
1. 두 테이블을  BOARD_ID 로 JOIN

2. 조회수가 가장 높은 행을 필터링한다 (서브쿼리 사용)

3. CONCAT을 활용해 컬럼들을 연결한다

4. FILE_ID로 내림차순 정렬한다


[정답]

SELECT CONCAT('/home/grep/src/', 
              B.BOARD_ID, '/', F.FILE_ID, F.FILE_NAME, F.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD B
JOIN USED_GOODS_FILE F
    ON B.BOARD_ID = F.BOARD_ID
WHERE B.VIEWS = (SELECT MAX(VIEWS) 
                 FROM USED_GOODS_BOARD)
ORDER BY F.FILE_ID DESC

[문제]
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.


[단계별 풀이]
1. NAME이 Yogurt이거나 Milk인 행들을 필터링

2. CART_ID 로 GROUP BY

3. NAME의 고유값들을 COUNT

4. COUNT 한 값이 2이상인 행들만 필터링

5. CART_ID를 조회

6. CART_ID로 오름차순 정


[정답]

SELECT CART_ID
FROM CART_PRODUCTS
WHERE NAME IN ('Yogurt', 'Milk')
GROUP BY CART_ID
HAVING COUNT(DISTINCT NAME) >= 2
ORDER BY CART_ID

[문제]
이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.


[단계별 풀이]

1. HOST_ID를 GROUP BY 해서 ID를 COUNT

2. COUNT 한 수가 2이상인 HOST_ID만 필터링

3. ID, NAME, HOST_ID 를 조회

4. ID 순으로 정렬 


[정답]

SELECT ID,
       NAME,
       HOST_ID
FROM PLACES
WHERE HOST_ID IN (SELECT HOST_ID
                  FROM PLACES
                  GROUP BY HOST_ID
                  HAVING COUNT(ID) >= 2)
ORDER BY ID

[문제]
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. 대여 기간 컬럼을 하나 만들고

2. CAR_ID로 GROUP BY 한 뒤 해당 컬럼을 AVG

3. AVG 한 값이 7일 이상인 행들만 필터링

4. CAR_ID와 평균 대여 기간 조회

5. 평균 대여 기간을 반올림해주고, 내림차순 정렬, 자동차 ID도 내림차순 정


[정답]

SELECT CAR_ID,
       ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

[문제]
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. START_DATE 가 2022년 9월인 행들을 필터

2. END_DATE와 START_DATE의 차이 + 1 (빌리는 당일도 기간에 포함)
3. 위의 컬럼이 30일 이상인 행들을 장기 대여 그렇지 않으면 단기 대여 표시 (RENT_TYPE 컬럼) 

4. 대여 기록 ID를 내림차순 정렬

[정답]

SELECT HISTORY_ID,
       CAR_ID,
       DATE_FORMAT(START_DATE, '%Y-%m-%d') START_DATE,
       DATE_FORMAT(END_DATE, '%Y-%m-%d') END_DATE,
       CASE
           WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '장기 대여'
           ELSE '단기 대여'
       END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC

[문제]
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.


[단계별 풀이]
1. 두 테이블을 REST_ID 기준으로 JOIN

2. 주소가 서울인 행을 필터링

3. 식당ID, 이름, 종류, 즐찾수, 주소를 GROUP BY → 하나의 식당이 나옴

4. 그룹화한 리뷰점수들을 평균

5. 평균점수를 내림차순, 즐찾수를 내림차순 정렬

[정답]

SELECT R.REST_ID,
       R.REST_NAME,
       R.FOOD_TYPE,
       R.FAVORITES,
       R.ADDRESS,
       ROUND(AVG(RE.REVIEW_SCORE), 2) AS SCORE
FROM REST_INFO R
JOIN REST_REVIEW RE
    ON R.REST_ID = RE.REST_ID
WHERE ADDRESS LIKE '서울%'
GROUP BY R.REST_ID,
         R.REST_NAME,
         R.FOOD_TYPE,
         R.FAVORITES,
         R.ADDRESS
ORDER BY SCORE DESC, FAVORITES DESC

[문제]
USER_INFO (I) 테이블과 ONLINE_SALE (S) 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.


[단계별 풀이]
1. 테이블을 USER_ID를 기준으로 JOIN

2. 성별이 NULL 인 행은 제외

3. 년, 월, 성별을 GROUP BY

4. 고유한(DISTINCT) S.USER_ID 를 COUNT

5. 년 월 성별 기준으로 오름차순


[정답]

SELECT YEAR(S.SALES_DATE) YEAR,
       MONTH(S.SALES_DATE) MONTH,
       GENDER,
       COUNT(DISTINCT S.USER_ID) USERS
FROM USER_INFO I
JOIN ONLINE_SALE S 
    ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER