241220 TIL 데이터분석과정

1. SQL 코드카타

문제1. 상품을 구매한 회원 비율 구하기

SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
        DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
        COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%'), 1) AS PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

1. 우선 user table과 online table을 user_id로 join을 시킨 뒤 where절로 2021년 가입한 회원들만 추려준다.
2. 문제에서 원하는 년도와 월은 DATE_FORMAT()을 이용하여 추출한 뒤 column명을 year, month로 지정하여 나중에 group by (column명)을 깔끔하게 해 준다.
3. 년, 월별로 그룹을 묶어준 뒤 해당 월에 구입한 회원 총 수를 count()를 이용해 구해준다.
4. (주의) join 된 테이블 결과는 한 회원이 여러 번 구매 이력이 있을 수 있기 때문에 distinct를 이용하여 중복 회원 id를 제거해 준 뒤 count를 세줘야 한다.
5. 상품 구매 회원 비율을 구하기 위해 2021년에 가입한 총 회원 수를 구해줘야 된다.
6. (SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%') 서브 쿼리를 이용해 간단하게 구해준다.
7. 문제에서 둘째 자리에서 반올림을 해줘야 하기 때문에 round() 이용해 준다.
8. 문제에서 원하는 대로 order by

 

문제2. 

WITH value AS (
    SELECT car.daily_fee, car.car_type, his.history_id,
           DATEDIFF(end_date, start_date) + 1 AS period,
    CASE 
      WHEN DATEDIFF(end_date, start_date) + 1 >= 90 THEN '90일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 30 THEN '30일 이상'
      WHEN DATEDIFF(end_date, start_date) + 1 >= 7 THEN '7일 이상'
      ELSE 'NONE' END AS duration_type
FROM car_rental_company_rental_history AS his
INNER JOIN car_rental_company_car AS car ON car.car_id = his.car_id
WHERE car.car_type = '트럭')   



SELECT value.history_id, 
    ROUND(value.daily_fee * value.period * 
          (100 - IFNULL(plan.discount_rate,0)) / 100) AS FEE
FROM value
LEFT JOIN car_rental_company_discount_plan AS plan 
    ON plan.duration_type = value.duration_type 
    AND plan.car_type = value.car_type
ORDER BY 2 DESC, 1 DESC

 

 

 

2. QCC

당신의 회사는 지난 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 회사 제품을 주문한 고객을 조회하고 구매수량별로 고객등급을 매기려 합니다. 결과는 총 주문 수량을 내림차순으로 정렬해주세요.

Sales_SalesOrderHeader, Sales_SalesOrderDetail, Sales_Customer, Person_Person 테이블을 사용하여 다음 조건을 만족하는 고객 목록을 구하세요:

1. 주문 날짜가 “2011-10-01 부터 2011-10-31 사이”에 해당
2. 고객의 총 주문 수량에 따라 5가지 등급으로 분류
   1. VIP : 100 개 이상
   2. GOLD : 70 개 이상 100 개 미만
   3. SILVER : 40 개 이상 70 개 미만
   4. BRONZE : 20 개 이상 40 개 미만
   5. BASIC : 20 개 미만
3. 해당 고객들의 기본 정보를 포함, 총 주문 수량 기준 내림차순 정렬하여 출력

---

출력 값 예시

customer_id (고객 ID), first_name (이름), last_name (성),

total_quantity (총 주문 수량), customer_class (고객 등급)

 

 

✅풀이방법

 

답안1. 서브쿼리를 이용한 답안

#with절로 2011년 10월에 주문한 내역을 먼저 찾아준다. 

WITH CustomerOrders AS (
    SELECT 
        SC.CustomerID,
        PP.FirstName, 
        PP.LastName,
        SUM(OD.OrderQty) AS total_quantity
    FROM Sales_Customer AS SC
    JOIN Person_Person AS PP
        ON SC.PersonID = PP.BusinessEntityID
    JOIN Sales_SalesOrderHeader AS OH
        ON SC.CustomerID = OH.CustomerID
    JOIN Sales_SalesOrderDetail AS OD
        ON OH.SalesOrderID = OD.SalesOrderID
    WHERE DATE_FORMAT(OH.OrderDate,'%Y-%m-%d') BETWEEN '2011-10-01' AND '2011-10-31' 
    GROUP BY SC.CustomerID, PP.FirstName, PP.LastName
)
SELECT 
    CustomerID,
    FirstName,
    LastName,
    total_quantity,
    CASE 
        WHEN total_quantity >= 100 THEN 'VIP'
        WHEN total_quantity >= 70 THEN 'GOLD'
        WHEN total_quantity >= 40 THEN 'SILVER'
        WHEN total_quantity >= 20 THEN 'BRONZE'
        ELSE 'BASIC'
    END AS customer_class
FROM CustomerOrders
ORDER BY total_quantity DESC;

 

 

답안2. (모범답안): 서브쿼리를 이용하지 않은 답안

SELECT SC.CustomerID as customer_id,
	   PP.FirstName as first_name,
	   PP.LastName as last_name,
	   SUM(OD. OrderQty) AS total_quantity,
	   case when SUM(OD. OrderQty) >= 100 then 'VIP'
		  	when SUM(OD. OrderQty) >= 70 then 'GOLD'
		  	when SUM(OD. OrderQty) >= 40 then 'SILVER'
		  	when SUM(OD. OrderQty) >= 20 then 'BRONZE'
		  	else 'BASIC' end  AS customer_class
FROM Sales_Customer AS SC JOIN Person_Person AS PP
						   ON SC.PersonID = PP.BusinessEntityID 
						  JOIN Sales_SalesOrderHeader AS OH
						   ON SC.CustomerID = OH.CustomerID 
						  JOIN Sales_SalesOrderDetail AS OD
						   ON OH.SalesOrderID = OD.SalesOrderID 
WHERE Date(OH.OrderDate) BETWEEN '2011-10-01' AND '2011-10-31'
GROUP BY SC.CustomerID , PP.FirstName, PP.LastName 
ORDER BY total_quantity DESC;

 

💟헷갈리는 개념

 

1. between  A and B: 사이의 값을 계산해주므로, 해당 값을 포함해준다.

다만, 시분초 개념을 나타낼 때는 만약 날짜 데이터를 사용하는데 시분초가 없이 지금처럼 날짜만 이용한다면 00시 00분 00초로 자동으로 인식된다. 즉 '2020-07-15' 는 '2020-07-15 00:00:00' 로 컴퓨터가 인식한다는 의미. 

따라서, DATE함수를 이용해서 날짜만 출력하게끔 형태를 변경해야 한다. 

 

2.

>= a 이상 

<= a 이하

> a 이상

< a 이하

 

3. 반드시 서브쿼리를 작성해줄 필요는 없다.