TIL_20240102 (개인과제 1차)

문제 1 : "최소 주문 날짜 찾기"

select MIN(STR_TO_DATE(order_date, '%d-%m-%Y')) as MIN_DATE
from list_of_orders

문제 2 : "카테고리별 총 매출 계산하기"

SELECT Category,
	   SUM(Amount) as TOTAL_AMOUNT 
FROM order_details od 
group by Category

문제 3 : "상위 5명의 고객별 총 구매액 찾기"

SELECT CustomerName ,
	   SUM(amount) AS TOTAL_AMOUNT
FROM list_of_orders loo, order_details od 
where loo.order_id = od.order_id
group by 1
order by 2 desc
limit 5;

문제 4-1 : "여러 주문을 한 고객들의 총 구매액 계산하기"

SELECT CustomerName,
	   count(CustomerName) as Buy_count,
	   SUM(amount) AS TOTAL_AMOUNT
FROM list_of_orders loo, order_details od 
where loo.order_id = od.order_id
group by CustomerName
having count(CustomerName) >= 3

[해설] "여러 주문을 한 고객들의 총 구매액 계산하기"
list_of_orders, order_details 테이블을 order_id로 inner join하여 customername별로 그룹화함
→ 한 고객이 주문한 횟수를 카운트하고, 총 구매액을 합함
→ 이후 having절을 통해 Buy_count가 3번 이상인 고객들로만 조건을 걸었음

문제 5-1 : "평균 수량을 초과하는 주문 찾기"

SELECT order_id, Amount, Profit , Quantity , od.Category, sub_category 
from order_details od left join (
	SELECT Category, avg(Quantity) as AVG_quantity
	FROM order_details
	group by 1
	) od2 on od.Category = od2.category
where od.Quantity > od2.AVG_quantity

[해설] "평균 수량을 초과하는 주문 찾기"
1. 인라인뷰 서브쿼리를 통해 카테고리별 주문량 평균을 구함
2. 1번의 서브쿼리를 order_details에 category로 left join하여 평균 주문량을 비교할 수 있도록 함
3. where 절을 통해 각 주문마다 평균 주문량을 초과하는 값만 가져오도록 조건을 걺

문제 6-1 : "카테고리별 매출액 순위 및 누적합계 계산하기"

SELECT order_id, category,
	   rank() over (PARTITION BY Category order by amount desc) AS '순위',
	   sum(amount) over (PARTITION BY Category order by amount desc) AS '누적 매출액' 
FROM order_details od

[해설] "카테고리별 매출액 순위 및 누적합계 계산하기"
1. order_details 에서 window rank 함수를 사용하여 category별 주문액 내림차순으로 순위 매김
2. window sum함수를 사용하여 category별 주문액 내림차순으로 누적금액 집계
3. order_id, category 컬럼을 추가하여 식별가능토록 함

[쿼리] 문제 7-1 : "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

select state, category,
	   rank() over (PARTITION  BY state order by sum_amount desc) as 'RANK',
	   if(sum_amount > '50%_target', 'Y', 'N')'50%_Target_sucess(Y/N)'
from
(
SELECT state, od.category, sum(amount) sum_amount,
	   sum(profit) sum_profit, avg(Target)*0.5 as '50%_target'
FROM list_of_orders loo  join order_details od on loo.order_id = od.order_id
		 				 join sales_target st on od.Category = st.Category
group by 1, 2) a
group by 1, 2

[해설] "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"
1. 인라인뷰 서브쿼리를 통해 list_of_orders, order_details를 order_id, category 컬럼으로 조인
2. 서브쿼리 내에서 state와 category를 그룹화, 주문액 합계, 이익합계, 타겟목표 50%를 컬럼으로 가짐
3. 서브쿼리를 FROM 테이블로 두고 주 별, 카테고리별 그룹화
4. 주 별 랭크를 window rank 함수로 주별(파티션) 총 주문액 내림차순으로 순위매김
5. if함수로 타겟 50%를 넘는지 체크하여 Y, N 으로 표기함

[쿼리] 문제 8-1 : 고객 평생 가치(CLV) 분석

SELECT main.customername, total_amount, total_profit, puc_count, datediff as '고객수명',
	   total_amount/puc_count as '평균구매가치',
	   ROUND(puc_count/(select count(DISTINCT customername) from list_of_orders loo)*100,2) '평균구매빈도',
	  ROUND((total_amount/puc_count) * total_profit * puc_count/(select count(DISTINCT customername) from list_of_orders loo) * datediff) CLV
from 
	(select customername,
	sum(amount) total_amount,
	sum(profit) total_profit,
	count(1) 'puc_count'
	from list_of_orders loo left join (select order_id, amount, profit from order_details od) od on loo.order_id = od.order_id
	group by 1) main left join (SELECT customername,
			    						 if(count(*)>1, timestampdiff(day,Min(order_date), Max(order_date)),0) as datediff
						 		FROM 
									(SELECT  order_id, CustomerName, str_to_date(order_date, '%d-%m-%Y') as order_date
									 FROM list_of_orders loo
									 GROUP BY 1) a
								  	 group by 1) sub on main.customername = sub.customername
ORDER BY CLV DESC

[해설] 고객 평생 가치(CLV) 분석
1. 인라인뷰에서 list_of_orders와 order_details의 서브쿼리를 이용하여 고객별 총 주문액, 총 이익, 구매횟수를 구함, main 서브쿼리로 지정
2. 고객수명을 추가하기 위해 서브쿼리로 list_of_orders의 order_date를 date type으로 바꾸고 구매 시작일부터 마지막 구매일까지의 기간을 고객별로 구하고 sub 서브쿼리로 지정
3. main, sub 두 서브쿼리를 main에 left join하여 계산식에 필요한 컬럼을 모두 가져옴
4. join 테이블을 이용하여 고객별 총 주문액, 총 이익, 고객수명, 평균구매가치, 평균구매빈도를 구하여 마지막 컬럼에 고객평생가치(CLV)를 계산함
5. order by를 통해 고객평생가치 내림차순으로 정렬

_실전 문제1 : RFM

RFM 분석

마케팅 부서에서 RFM(Recency, Frequency, Monetary) 모델을 사용하여 고객 세분화 자료를 요청했어요

RFM 분석은 다음과 같은 세 가지 주요 요소를 기반으로 고객을 세분화하는 방법입니다
1. Recency (최근성): 고객이 마지막으로 구매한 시점. 이는 고객의 활동도를 나타냅니다.
2. Frequency (빈도): 고객이 특정 기간 동안 얼마나 자주 구매하는지. 이는 고객의 충성도를 반영합니다.
3. Monetary (금전적 가치): 고객이 기간 동안 얼마나 많은 돈을 지출했는지. 이는 고객의 가치를 나타냅니다.
각 고객은 이 세 가지 기준에 따라 점수를 받으며, 이 점수를 통해 다양한 마케팅 전략을 수립할 수 있습니다.

아래 쿼리는 'combined_orders' 뷰를 통해 각 고객별로
최근 구매일, 구매 빈도, 총 구매 금액을 계산하고,
이를 바탕으로 'customer_grouping' 뷰에서 고객을 RFM 모델에 따라 세분화를 진행하는 코드입니다.

이제 아래의 쿼리를 글로 동작 방법에 대해 자세히 설명하고,
해당 SQL 쿼리의 작동 방식을 시각적으로 표현하여 제출 해주세요!

쿼리

CREATE VIEW combined_orders AS
SELECT d.order_id, d.Amount, d.Profit, d.Quantity, d.Category, d.sub_category, l.order_date, l.CustomerName, l.State, l.City
FROM order_details AS d
INNER JOIN list_of_orders AS l 
ON d.order_id = l.order_id; 

CREATE VIEW customer_grouping AS
SELECT
  *,
  CASE 
    WHEN (R>=4 AND R<=5) AND (((F+M)/2) >= 4 AND ((F+M)/2) <= 5) THEN 'Champions' 
    WHEN (R>=2 AND R<=5) AND (((F+M)/2) >= 3 AND ((F+M)/2) <=5) THEN 'Loyal Customers'
    WHEN (R>=3 AND R<=5) AND (((F+M)/2) >= 1 AND ((F+M)/2) <=3) THEN 'Potential Loyalist'
    WHEN (R>=4 AND R<=5) AND (((F+M)/2) >= 0 AND ((F+M)/2) <=1) THEN 'New Customers'
    WHEN (R>=3 AND R<=4) AND (((F+M)/2) >= 0 AND ((F+M)/2) <=1) THEN 'Promising'
    WHEN (R>=2 AND R<=3) AND (((F+M)/2) >= 2 AND ((F+M)/2) <=3) THEN 'Customers Needing Attention'
    WHEN (R>=2 AND R<=3) AND (((F+M)/2) >= 0 AND ((F+M)/2) <=2) THEN 'About to Sleep'
    WHEN (R>=0 AND R<=2) AND (((F+M)/2) >= 2 AND ((F+M)/2) <=5) THEN 'At Risk'
    WHEN (R>=0 AND R<=1) AND (((F+M)/2) >= 4 AND ((F+M)/2) <=5) THEN "Can't Lose Them"
    WHEN (R>=1 AND R<=2) AND (((F+M)/2) >= 1 AND ((F+M)/2) <=2) THEN 'Hibernating'
    WHEN (R>=0 AND R<=2) AND (((F+M)/2) >= 0 AND ((F+M)/2) <=2) THEN 'Lost'
  END AS customer_segment
FROM (
  SELECT
    MAX(STR_TO_DATE(order_date, '%d-%m-%Y')) AS latest_order_date,
    CustomerName,
    DATEDIFF(STR_TO_DATE('31-03-2019', '%d-%m-%Y'), MAX(STR_TO_DATE(order_date, '%d-%m-%Y'))) AS recency,
    COUNT(DISTINCT order_id) AS frequency,
    SUM(Amount) AS monetary,
    NTILE(5) OVER (ORDER BY DATEDIFF(STR_TO_DATE('31-03-2019', '%d-%m-%Y'), MAX(STR_TO_DATE(order_date, '%d-%m-%Y'))) DESC) AS R,
    NTILE(5) OVER (ORDER BY COUNT(DISTINCT order_id) ASC) AS F, 
    NTILE(5) OVER (ORDER BY SUM(Amount) ASC) AS M 
  FROM combined_orders
  GROUP BY CustomerName
) rfm_table
GROUP BY CustomerName;


-- return the number & percentage of each customer segment
SELECT 
    customer_segment,
    COUNT(DISTINCT CustomerName) AS num_of_customers,
    ROUND(COUNT(DISTINCT CustomerName) / (SELECT COUNT(*) FROM customer_grouping) * 100, 2) AS pct_of_customers
FROM customer_grouping
GROUP BY customer_segment
ORDER BY pct_of_customers DESC;

텍스트 해설
<combined_orders 뷰테이블>
1. order_details와 list_of_orders를 order_id로 inner join
2. order_id, Amount, Profit, Category, sub_category, order_date, customer name, state, city 컬럼으로 뷰 생성

<customer_grouping 뷰테이블>
1. 인라인뷰 서브쿼리 활용하여 combined_orders(뷰테이블)를 customerName으로 그룹화
2. str_to_date함수와 MAX함수를 사용하여 latest_order_date(최근날짜), customername, datediff함수로 집계 기간중 마지막 날인 2019-3-31과 최근날짜의 차이는 recency(최근성), 중복없이 order_id의 카운트는 Frequency(빈도), 총주문액으로 monetary(금전적 가치) 컬럼으로 SELECT, 최근성(내림차순), 빈도(올림차순), 금전적 가치(올림차순)는 R, F, M로 하여 ntile 함수를 사용하여 5개씩 범주화함
3. 위 서브쿼리를 rfm_table로 지정
4. customername으로 그룹화
5. SELECT절에 모든 컬럼을 넣고 + case when 함수를 사용하여 고객별로 RFM에 따른 등급으로 범주화한 컬럼을 추가하고 컬럼명 customer_segment 로 지정

<최종 쿼리문>
1. Customer_grouping 테이블에서 customer_segment로 그룹화
2. SELECT절에 customer_segment, customername을 중복없이 카운트하여 num_of_customers 컬럼명 지정, num_of_customer를 customer_grouping 테이블의 전체 카운트로 나눈 값을 100곱하여 전체 등급 카운트중에 해당 등급의 비중을 표현하고 ROUND함수로 소숫점 두번째 자리까지만 표기, 컬럼명은 pct_of_customers로 지정
3. ORDER BY절에 pct_of_customers 내림차순 정렬