240814(+69) | SQL 코드카타 | 69~75

청솔·2024년 8월 14일
sql내배캠코드카타

SQL

목록 보기
20/23

Q.69 차량 대여 횟수 😫

2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성. 즉, 8월에서 10월까지 총 대여 횟수가 5대 이상인 자동차

STEP_1 8월에서 10월까지 차량 대여 기록 일부

SELECT  MONTH(START_DATE) 'MONTH', DAY(START_DATE) 'DAY', CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
ORDER BY 1 ASC, 3 DESC

STPE_2 특정 차량의 월별 대여 횟수 확인 --> 문제에서는 '특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외'라 요구 했으나 상관 없는 것 같다.

  • CAR_ID 29의 대여 횟수 - 8월: 4회 / 9월: 0회 / 10월: 0회

  • CAR_ID 27의 대여 횟수 - 8월: 5회 / 9월: 1회 / 10월: 0회

문제의 요구 조건(특정 월에 대여 횟수가 0인 차량은 결과에서 제외)에 따라 총 대여 횟수와 관계 없이 위의 두 차량은 결과에서 배제 되어야 한다.

STEP_3 8월~10월 동안 총 차량 대여 횟수가 5이상인 차량

SELECT CAR_ID
    , COUNT(*) AS 'RECORDS' 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
GROUP BY CAR_ID
HAVING RECORDS >= 5

STEP_4 총 차량 대여 횟수가 5이상인 차량의 월별 대여 횟수

WITH CAR_5 AS (
    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(*) >= 5
)
# main_query
SELECT MONTH(START_DATE) 'MONTH'
    , CAR_ID
    , COUNT(*) AS 'RECORDS' 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
JOIN CAR_5 USING (CAR_ID)
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31' 
GROUP BY MONTH, CAR_ID
ORDER BY 1 ASC, 2 DESC

Q.70 우수 회원 리뷰 출력 ✏️

  • 리뷰를 가장 많이 작성한 회원들의 리뷰들을 조회
  • 회원 이름, 리뷰 텍스트, 리뷰 작성일 출력
  • 리뷰 작성일을 기준으로 오름차순, 같다면 리뷰 텍스트를 기준으로 오름차순

STEP_1 DENSE_RANK 내장함수로 순위 매기기

    SELECT 
        MEMBER_ID,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS RNK
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY RNK DESC

STEP_2 가장 많이 리뷰를 작성한 회원의 모든 리뷰를 출력 (WITH CTE)

WITH TOP_MEM AS(
    SELECT 
        MEMBER_ID,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS RNK
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY RNK DESC
)
# main_query
SELECT m.MEMBER_NAME
	 , r.REVIEW_TEXT
     , DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') REVIEW_DATE
FROM MEMBER_PROFILE m
JOIN REST_REVIEW r USING(MEMBER_ID)
WHERE MEMBER_ID IN(
    SELECT MEMBER_ID FROM TOP_MEM
    WHERE RNK = 1
)
ORDER BY 3 , 2

Q.71 판매 데이터 통합 ✅

  • 2022년 3월의 오프라인+온라인 통합 상품 판매 데이터의
  • 판매날짜, 상품ID, 유저ID, 판매량을 출력
  • OFFLINE_SALE 판매 데이터의 USER_ID는 NULL로 표시 (NULL로 처리라는게 더 정확)
  • 판매일 기준으로 오름차순, 같다면 상품 ID를 기준으로 오름차순, 같아면 유저ID 오름차순

STEP_1 테이블을 위아래로 UNION ALL

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') SALES_DATE
     , PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE 
UNION ALL
SELECT SALES_DATE
     , PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE ('2022-03%')
ORDER BY 1, 2, 3

  • 동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재한다고 명시되어 있어 중복제거 없은 UNION ALL을 사용.
  • OFFLINE_SALE의 USER_ID를 NULL로 처리 (문제에는 NULL로 표시라 했지만, 정확하겐 처리다)
  • OFFLINE_SALE의 3월 데이터는 존재하지 않는다.

Q.72 조건에 맞는 댓글 조회

  • 2022년 10월에 작성된 게시글의 (댓글의 작성일자가 아니다!)
  • 제목, 게시글ID, 댓글ID, 댓글작성자ID, 댓글 내용, 댓글 작성일
  • 댓글 작성일을 기준으로 오름차순, 같다면 게시글 제목 기준으로 오름차순
SELECT TITLE, BOARD_ID, gr.REPLY_ID, gr.WRITER_ID, gr.CONTENTS
     , DATE_FORMAT(gr.CREATED_DATE, '%Y-%m-%d') CREATED_DATE
FROM USED_GOODS_BOARD gb
JOIN USED_GOODS_REPLY gr
USING (BOARD_ID)
WHERE gb.CREATED_DATE LIKE ('2022-10%')
ORDER BY CREATED_DATE, TITLE
  • 댓글이 작성된 게시글만 조회하기 위해 (INNER) JOIN
  • 조건은 게시글의 작성 일자이고, 정렬은 댓글의 작성 일자이다.

Q.73 시간 별 입양 횟수 ✏️

  • 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성
  • 이 문제의 핵심은, 원본 데이터에 모든 시간대별에 입양이 발생되지 않았다는 점.

STEP_1 WITH 'RECURSIVE(재귀쿼리)' CTE를 활용해 시간 테이블 생성

WITH RECURSIVE hours AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 
    FROM hours 
    WHERE HOUR < 23
)
SELECT * FROM hours;

STEP_2 시간대별 입양 횟수 집계

WITH RECURSIVE hours AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 
    FROM hours 
    WHERE HOUR < 23
)
# main_query
SELECT h.HOUR
    , COUNT(a.ANIMAL_ID)AS COUNT
FROM hours h
LEFT JOIN ANIMAL_OUTS a ON h.HOUR = HOUR(a.DATETIME)
GROUP BY 1
ORDER BY 1

Q.74 자동차 대여 비용 ✅

  • 자동차의 종류가 세단 또는 SUV인 자동차 중
  • 2022년 11월 1일~2022년 11월 30일까지 한달 내에 대여 가능
  • 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차의
  • 자동차 ID, 자동차 종류, 대여금액(FEE)을 출력
  • 정렬: 대여 금액 내림차순 > 차종 오름차순 > 자동차 ID 내림차순

STEP_1 차량별 11월에 대여 가능한 목록

  • 세단과 SUV 차량의 가장 마지막 대여 기록을 기준으로
  • 11월 이전에 대여가 끝나서 차고지에 대기 중인 차량

SELECT
    c.CAR_ID,
    c.CAR_TYPE,
    c.DAILY_FEE
    , DATE_FORMAT(h.START_DATE, '%Y-%m-%d') START_MONTH
    , DATE_FORMAT(h.END_DATE, '%Y-%m-%d') END_MONTH
FROM CAR_RENTAL_COMPANY_CAR c
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.CAR_ID = h.CAR_ID
WHERE c.CAR_TYPE IN ('세단', 'SUV')
GROUP BY c.CAR_ID
HAVING MAX(h.END_DATE) < '2022-11-01'
ORDER BY c.CAR_ID;

STEP_2 30일간의 할인율이 적용된 대여 금액 계산식

일별 요금 * 30 * (1 - (기간 별 할인율 * 0.01)
  • 1에서 할인율을 빼는 이유 -> 할인율을 제외하고 지불해야 하기 때문에.

STEP_3 차량 대여 기간 30일로 필터링

  • WHERE문에 대여 기간(DURATION_TYPE)을 30일 이상으로 필터링 안해주면, 가장 마지막으로 조회되는 90일 이상의 할인율이 적용된다.

SELECT
    c.CAR_ID,
    c.CAR_TYPE,
    d.DURATION_TYPE
    , FLOOR(c.daily_fee * 30 * (1 - d.discount_rate * 0.01)) as FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN CAR_RENTAL_COMPANY_CAR c ON c.CAR_ID = h.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON d.CAR_TYPE = c.CAR_TYPE
WHERE
    c.CAR_TYPE IN ('세단', 'SUV')
    AND d.DURATION_TYPE = '30일 이상'
GROUP BY c.CAR_ID
HAVING MAX(h.END_DATE) < '2022-11-01'

STEP_4 조건에 맞게 쿼리문 수정

SELECT
    c.CAR_ID
    , c.CAR_TYPE
    , FLOOR(c.daily_fee * 30 * (1 - d.discount_rate * 0.01)) as FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
JOIN CAR_RENTAL_COMPANY_CAR c ON c.CAR_ID = h.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d ON d.CAR_TYPE = c.CAR_TYPE
WHERE
    c.CAR_TYPE IN ('세단', 'SUV')
    AND d.DURATION_TYPE = '30일 이상'
GROUP BY c.CAR_ID
HAVING MAX(h.END_DATE) < '2022-11-01'
    AND FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

Q.75-1 차량 대여 금액

  • 트럭의 대여 기록에 대하여
  • 대여 기록 별 대여 금액을 계산
  • 대여 기록 ID와 대여 금액 출력
  • 정렬: 대여금액 내림차순 > 대여기록ID 내림차순

STEP_1 트럭 대여 리스트

SELECT h.HISTORY_ID
      , c.CAR_TYPE
      , c.DAILY_FEE
      , DATE_FORMAT(START_DATE,'%Y-%m-%d') START
      , DATE_FORMAT(END_DATE,'%Y-%m-%d') END
      , DATEDIFF(h.end_date,h.start_date)+1 'RENTAL_PERIOD'
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 d ON d.CAR_TYPE = c.CAR_TYPE
WHERE c.CAR_TYPE = '트럭'
GROUP BY h.HISTORY_ID

STEP_2 대여료 계산

DAILY_FEE * RENTAL_PERIOD * (1 - (discount_rate * 0.01)

STEP_3 기간별 대여료 계산

SELECT h.HISTORY_ID
      , c.CAR_TYPE
      , c.DAILY_FEE
      , DATE_FORMAT(START_DATE,'%Y-%m-%d') START
      , DATE_FORMAT(END_DATE,'%Y-%m-%d') END
      , DATEDIFF(h.end_date,h.start_date)+1 RENTAL_PERIOD
      , FLOOR(CASE
            WHEN DATEDIFF(h.end_date,h.start_date)+1 < 7 THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1)
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=7 AND DATEDIFF(h.end_date,h.start_date)+1 < 30 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (5 * 0.01))
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=30 AND DATEDIFF(h.end_date,h.start_date)+1 < 90 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (8 * 0.01))
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=90 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (15 * 0.01))
        END) 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 d ON d.CAR_TYPE = c.CAR_TYPE
WHERE c.CAR_TYPE = '트럭'
GROUP BY h.HISTORY_ID

STEP_4 조건에 맞게 쿼리문 수정

SELECT h.HISTORY_ID
      , FLOOR(CASE
            WHEN DATEDIFF(h.end_date,h.start_date)+1 < 7 THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1)
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=7 AND DATEDIFF(h.end_date,h.start_date)+1 < 30 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (5 * 0.01))
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=30 AND DATEDIFF(h.end_date,h.start_date)+1 < 90 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (8 * 0.01))
            WHEN DATEDIFF(h.end_date,h.start_date)+1 >=90 
            THEN c.DAILY_FEE * (DATEDIFF(h.end_date,h.start_date)+1) * (1 - (15 * 0.01))
        END) 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 d ON d.CAR_TYPE = c.CAR_TYPE
WHERE c.CAR_TYPE = '트럭'
GROUP BY h.HISTORY_ID
ORDER BY 2 DESC, 1 DESC
  • 수식에 괄호를 잘 해줘야 계산 오류가 나지 않는다 ^^..

STEP_5 쿼리 개선

SELECT 
    h.HISTORY_ID,
    FLOOR(
        c.DAILY_FEE * (DATEDIFF(h.END_DATE, h.START_DATE) + 1) * 
        CASE 
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 7 THEN 1
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 30 THEN 0.95
            WHEN DATEDIFF(h.END_DATE, h.START_DATE) + 1 < 90 THEN 0.92
            ELSE 0.85
        END
    ) AS FEE
FROM 
    CAR_RENTAL_COMPANY_CAR c
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h ON c.CAR_ID = h.CAR_ID
WHERE 
    c.CAR_TYPE = '트럭'
ORDER BY 
    FEE DESC, 
    h.HISTORY_ID DESC;
  • CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 최종쿼리에 쓰지 않았기에 반점짜리 풀이다.
profile
청솔
모든 사람이 쉽게 이해할 수 있는 데이터 분석을 지향하는 분석가가 되고 싶습니다. "데이터 분석은 사람을 설득 시킬 수단이다. "
이전 포스트

240812(+68) | SQL 코드카타 | 59~68

다음 포스트

240816(+72) | SQL 코드카타 | 75(COALESCE),76

0개의 댓글