코드 카타

문제 & 제출 답안

• 입양 시각 구하기(2)

  WITH RECURSIVE hour_count
  AS
  (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 AS hour_cnt
  FROM hour_count
  WHERE hour < 23
  )
  
  SELECT H.hour, IFNULL(count, 0) as count
  FROM hour_count H
  LEFT JOIN
          (
          SELECT HOUR(datetime) AS hour, COUNT(*) AS count
          FROM animal_outs
          GROUP BY 1
          ) A
  ON H.hour = A.hour

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

  SELECT DISTINCT c.car_id,
  			c.car_type,
  			TRUNCATE(c.daily_fee * (1 - d.discount_rate * 0.01) * 30,0) as FEE
  FROM CAR_RENTAL_COMPANY_CAR c
  LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
      ON c.car_type = d.car_type
  LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
      ON c.car_id = h.car_id
  WHERE c.car_type IN ('세단', 'SUV')
      AND c.car_id NOT IN
                      (
                          SELECT car_id
                          FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                          WHERE start_date <= '2022-11-30'
                              AND end_date >= '2022-11-01'
                      )
      AND SUBSTRING(d.duration_type, 1, 2) = 30
      AND ((c.daily_fee * (1 - d.discount_rate * 0.01) * 30) BETWEEN 500000 AND 2000000)
  ORDER BY 3 desc, 2, 1 desc

• 자동차 대여 기록 별 대여 금액 구하기

  WITH HISTORY AS
  (
      SELECT *, DATEDIFF(end_date, start_date) + 1 as days
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
  )
  
  SELECT history_id, TRUNCATE(IFNULL(days * daily_fee * (1 - discount_rate * 0.01), days * daily_fee), 0) FEE
  FROM
  (
  SELECT *,
      CASE WHEN days < 7 THEN NULL
           WHEN days < 30 THEN '7일 이상'
           WHEN days < 90 THEN '30일 이상'
           ELSE '90일 이상'
       END duration_type
  FROM HISTORY
  ) A
  INNER JOIN CAR_RENTAL_COMPANY_CAR C
      ON A.car_id = C.car_id
  LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
      ON C.car_type = D.car_type
      AND A.duration_type = D.duration_type
  WHERE C.car_type = '트럭'
  ORDER BY 2 DESC, 1 DESC

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

  SELECT YEAR(o.sales_date) year,
      MONTH(o.sales_date) month,
      COUNT(DISTINCT u.user_id) AS PURCHASED_USERS,
      ROUND(
          COUNT(DISTINCT u.user_id) / 
          (SELECT count(*)
          FROM user_info
          WHERE joined like '2021%'
          ),1) AS PURCHASED_RATIO
  FROM user_info u
  LEFT JOIN online_sale o
  ON o.user_id = u.user_id
  WHERE u.joined like '2021%'
  GROUP BY 1, 2
  HAVING year IS NOT NULL
  ORDER BY 1, 2

• 1757. Recyclable and Low Fat Products

  SELECT product_id
  FROM PRODUCTS
  WHERE low_fats = 'Y'
      AND recyclable = 'Y'

• 584. Find Customer Referee

  SELECT name
  FROM customer
  WHERE id NOT IN
              (
              SELECT id
              FROM customer
              WHERE referee_id = 2)

• 595. Big Countries

  SELECT name, population, area
  FROM world
  WHERE area >= 3000000
      OR population >= 25000000

• 1148. Article Views I

  SELECT distinct author_id as id
  FROM views
  WHERE author_id = viewer_id
  ORDER BY 1

오답노트

• WITH / WITH RECURSIVE

  WITH RECURSIVE hour_count
  AS
  (
  SELECT 0 AS hour
  UNION ALL
  SELECT hour + 1 AS hour_cnt
  FROM hour_count
  WHERE hour < 23
  )
  
  SELECT H.hour, IFNULL(count, 0) as count
  FROM hour_count H
  LEFT JOIN
          (
          SELECT HOUR(datetime) AS hour, COUNT(*) AS count
          FROM animal_outs
          GROUP BY 1
          ) A
  ON H.hour = A.hour

  • WITH 문
    • CTE 생성 구문
      • CTE란? 쿼리 내에서 일시적으로 사용가능한, 이름을 붙인 결과 집합 (가상테이블)
        
    • 장점
      • 가독성 & 유지보수성
      • 중복 제거
      • 성능 최적화
    • 사용 방법

      # 기본구조
      
      WITH 가상테이블명 AS
      (
      SELECT 출력 컬럼
      FROM 테이블
      )
      
      SELECT *
      FROM 가상테이블

  • WITH RECURSIVE
    • 재귀 쿼리 생성 구문
      • 재귀 쿼리란? 반복적으로 작동시킬 쿼리. 자료들의 계층 파악에 용이
        
    • 사용 방법

      # 기본 구조
      
      WITH RECURSIVE CTE AS
      (
      SELECT 0 AS N        -- 비반복 쿼리 (시작)
      UNION ALL
      SELECT N + 1 AS NUM  -- 반복 쿼리 (해당 위치에서 반복 및 종료)
      FROM CTE
      WHERE N < 5          -- 종료 조건
      )
      
      SELECT *
      FROM CTE

    • 결과값

      N
      0
      1
      2
      3
      4
      5

• 두 가지 이상 연결키로 JOIN, 새로운 컬럼 생성 후 JOIN
  • 정답

    WITH HISTORY AS
    (
        SELECT *, DATEDIFF(end_date, start_date) + 1 as days
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    )
    
    SELECT history_id, TRUNCATE(IFNULL(days * daily_fee * (1 - discount_rate * 0.01), days * daily_fee), 0) FEE
    FROM
    (
    SELECT *,
        CASE WHEN days < 7 THEN NULL
             WHEN days < 30 THEN '7일 이상'
             WHEN days < 90 THEN '30일 이상'
             ELSE '90일 이상'
         END duration_type
    FROM HISTORY
    ) A
    INNER JOIN CAR_RENTAL_COMPANY_CAR C
        ON A.car_id = C.car_id
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
        ON C.car_type = D.car_type
        AND A.duration_type = D.duration_type
    WHERE C.car_type = '트럭'
    ORDER BY 2 DESC, 1 DESC

  • 내가 쓴 답

    SELECT history_id,
        CASE WHEN days >= 90 then truncate(daily_fee * days * 85 * 0.01, 0)
             WHEN days BETWEEN 30 AND 89 then truncate(daily_fee * days * 92 * 0.01, 0)
             WHEN days BETWEEN 7 AND 29 then truncate(daily_fee * days * 95 * 0.01, 0)
        ELSE daily_fee * days
        END AS FEE
    FROM
    (
    SELECT h.history_id,
        h.car_id,
        c.car_type,
        c.daily_fee,
        DATEDIFF(h.end_date, h.start_date) +1 AS days,
        d.duration_type,
        d.discount_rate
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
    LEFT JOIN CAR_RENTAL_COMPANY_CAR c
        ON h.car_id = c.car_id
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
        ON c.car_type = d.car_type
    WHERE c.car_type = '트럭'
    ) A
    GROUP BY 1
    ORDER BY 2 desc, 1 desc

• 목표 : 대여 기록별 대여 금액 구하기

  1. 대여 금액 = 대여 일수 차량별 일일 대여 금액 할인율
     • 할인율이 없을 경우 IFNULL 로 처리
  2. 대여 기록 테이블에 일수에 맞는 할인율 컬럼을 붙여야 함.
     • 일수 구하기 (DATEDIFF(end_date, start_date) + 1)
     • 일수별 해당되는 할인율 카테고리 설정 (CASE WHEN)
     • LEFT(INNER(대여 기록 + 차량 정보) + 할인율)

       대여 기록	차량 정보	할인율
       Car_id	Car_id
       	Car_type	Car_type
       Duration_type		Duration_type

       • Car_type으로 1차 연결 (대여 기록별 3개의 할인율 컬럼 JOIN)
       • Duration_type으로 2차 연결 (대여 기록별 1개 할인율 컬럼 JOIN)
  3. 트럭인 데이터만 조회
     • WHERE절 활용

• SELECT 문 뒤 서브쿼리 활용

  SELECT YEAR(o.sales_date) year,
      MONTH(o.sales_date) month,
      COUNT(DISTINCT u.user_id) AS PURCHASED_USERS,
      ROUND(
          COUNT(DISTINCT u.user_id) / 
          (SELECT count(*)
          FROM user_info
          WHERE joined like '2021%'
          ),1) AS PURCHASED_RATIO
  FROM user_info u
  LEFT JOIN online_sale o
  ON o.user_id = u.user_id
  WHERE u.joined like '2021%'
  GROUP BY 1, 2
  HAVING year IS NOT NULL
  ORDER BY 1, 2

  • 구해야 할 것 : 구매 회원 수 / 전체 가입 회원 수

    • 구매 회원 수 : COUNT(DISTINCT user_id)

    • 전체 가입 회원 수 : SELECT COUNT(*) FROM user_info)

      ⇒ COUNT(DISTINCT user_id) / SELECT COUNT(*) FROM user_info)