SQL_Practice5.sql

# 5주차 수업 - 필수과제
## 고객의 넘버랑 고객의 revenue, 이 고객들의 세그먼트 (20% , 80% 기준으로 나눠서 VVIP, VIP)

USE classicmodels;

select * from orders;    
select * from orderdetails; 

with order_revenue as ( # order 별 revenue
	select
		orderNumber,
		sum(quantityOrdered * priceEach) as order_revenue
	from orderdetails
	group by orderNumber
),
customer_revenue as ( # 고객 별 revenue 
select
    o.customerNumber,
    sum(r.order_revenue) as cust_revenue,
    percent_rank() over(order by sum(r.order_revenue) desc) as ranking
from orders as o
inner join order_revenue as r
on o.orderNumber = r.orderNumber
group by customerNumber
)
select
	customerNumber,
    cust_revenue,
    case
		when (round(ranking,3) <= 0.2) then 'VVIP' # 상위 20%
        when (round(ranking,3) <= 0.8) then 'VIP'  # 상위 80%
        else '이탈가능성 있음'
	end as seg_rank
from customer_revenue;


## cf)
### PERCENT_RANK 함수 : RANK 결과값을 백분율 순위를 계산, 해석하자면, 자신보다 아래 전체의 몇 퍼센트가 있다는 의미이다.(less than)
### CUME_DIST() 함수 : 누적분포를 의미 즉, 자신을 포함하여 전체의 몇 퍼센트가 있다는 의미이다. ( less or equal )