[SQL][개인과제] ECOMMERCE 데이터 분석(2)_중급~상급 문제

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

두 테이블 **list_of_orders**와 **order_details**가 있습니다. list_of_orders 테이블은 고객 이름과 주문 ID를, order_details 테이블은 각 주문의 상세 금액을 포함하고 있습니다. 이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름, 총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.

> count할 때, 여러 번 나오는 경우 distinct를 고려

select lo.CustomerName ,
	  count(distinct lo.order_id) as count_o,
	  sum(od.Amount) as sum_a
from ecommerce.list_of_orders lo join  ecommerce.order_details od
	on lo.order_id = od.order_id
group by lo.CustomerName  
having count(lo.order_id) >= 3;

 

 

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

order_details 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다. 이 테이블의 각 행은 주문 ID(order_id), 카테고리(Category), 그리고 주문 수량(Quantity)을 포함합니다. 이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는 모든 주문들을 찾아보세요.

> 셀프조인 이해

SELECT order_id, category, quantity
FROM ecommerce.order_details A join
	 (select category, AVG(Quantity) avg_quantity
	  from ecommerce.order_details od 
	  group by category) b
	  on a.category = b.category
where quantity > avg_quantity

 

 

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

order_details 테이블에는 주문의 상세 정보가 포함되어 있습니다. 이 테이블은 주문 ID(order_id), 카테고리(Category), 그리고 주문의 금액(Amount)을 포함합니다. 이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 그 카테고리 내의 누적 매출액을 계산하세요.

select Category ,
	   order_id ,
	   rank() over(partition by category order by sum(amount) desc) rank_amount,
	   sum(Amount) as sum_amount,
	   sum(sum(amount)) over(partition by category order by sum(amount) desc) cum_sum_amount
from ecommerce.order_details od 
group by (category, order_id) ;

 

 

문제 7: "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

list_of_orders, order_details, 그리고 sales_target 세 개의 테이블이 있습니다. list_of_orders 테이블은 주문 ID(order_id)와 주문이 이루어진 주(State)를, order_details 테이블은 각 주문의 금액(Amount)과 이익(Profit)을, sales_target 테이블은 각 카테고리별 매출 목표(Target)를 포함합니다. 이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고, 각 주 내에서 주문의 매출 순위를 결정하세요. 또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

 

select *,
	case when total_m > avg_target/2 then 'o'
	else 'x' end 'Y/N' -- 4)case 이용
from ( -- 1)total_m과 total_p, 순위 구하기
	select state,
		category,
	 	sum(amount) total_m,
	 	sum(profit) total_p,
	 	rank() over(partition by state order by sum(amount) desc) rank_m
	from ecommerce.order_details od join ecommerce.list_of_orders loo
		on od.order_id = loo.order_id
	group by loo.State, od.category
	) A join
	( -- 2)카테고리별 target 평균
	select Category ,
		 avg(Target) avg_target
	from ecommerce.sales_target st
	group by category
	) B
	on A.category = B.category -- 3)조인 후
order by category, rank ;

 

 

문제 8: 고객 평생 가치(CLV) 분석 _ 아직 못 푼 문제

당신은 list_of_orders와 order_details 두 테이블을 가지고 있으며, 이를 통해 고객별 평생 가치를 분석하고자 합니다. list_of_orders 테이블은 고객 이름(CustomerName), 주문 날짜(order_date), 주문 ID를 포함하고, order_details 테이블은 각 주문의 금액(Amount)과 수익(Profit)을 포함합니다. 고객별로 총 구매액, 총 주문 횟수, 주문 간 평균 일수를 계산하고, 이를 기반으로 고객의 평생 가치를 분석하세요.

> 주문 간 평균 일수를 구하지 못함.. 첫 날부터 마지막 날 / 주문 횟수 인가..?

-- 고객별 총 구매액, 총 주문 횟수까지만
select loo.customername,
	   sum(od.amount) sum_amount,
	   count(loo.CustomerName) count_o
from ecommerce.order_details od join ecommerce.list_of_orders loo 
	 on od.order_id = loo.order_id 
group by loo.CustomerName ;