[SQL/Classicmodels Database] 문제 10,문제 11 (평균 주문 금액이 높은 고객군의 주문 패턴 분석,신규 고객 유입 및 초기 매출 분석)

문제 10: 평균 주문 금액이 높은 고객군의 주문 패턴 분석

전체 고객 중 평균 주문 금액 상위 25%에 해당하는 고객군을 추출하고, 이들 고객의 연간 주문 횟수와 평균 주문 금액을 계산하세요.

• customers, orders, orderdetails 테이블을 사용하세요.
• 상위 25% 고객군에 대해 연도별 주문 횟수와 평균 주문 금액을 계산하세요.
• 출력: 연도, 고객 ID, 총 주문 횟수, 연평균 주문 금액

step 1. 전체 고객 중 평균 주문 금액 상위 25%에 해당하는 고객군 추출

percent_rank() 윈도우함수를 통해서 고객 평균 주문 금액 백분율을 구한 뒤, 상위 25%에 해당하는 고객군을 추출

with rank_25_table as 
(
select customerNumber
from
(
select customerNumber , percent_rank() over (order by avg_priceEach) as ranking
from (select a.customerNumber, avg(priceEach) as avg_priceEach
from customers a inner join orders b on a.customerNumber = b.customerNumber 
inner join orderdetails c on b.orderNumber = c.orderNumber
group by a.customerNumber) t
) t2
where ranking >=0.75
)

select * from rank_25_table;

step2. 고객의 연간 주문 횟수와 평균 주문 금액 추출하기

step1의 테이블과 order,orderdetail,customers 테이블 조인한 뒤,
연도별 연간 주문 횟수의 총 합과 평균 주문 금액 추출

select  year_date ,customerNumber, sum(quantityOrdered) as sum_quantityOrdered, round(avg(priceEach),2) as avg_priceEach

from
(
select a.customerNumber,year(orderDate) as year_date, priceEach, quantityOrdered
from customers a inner join orders b on a.customerNumber = b.customerNumber 
inner join orderdetails c on b.orderNumber = c.orderNumber inner join rank_25_table d 
on b.customerNumber = d.customerNumber 
) t
group by  year_date, customerNumber
order by 1,2 ;

문제 11: 신규 고객 유입 및 초기 매출 분석

새로운 고객이 최초로 주문한 시점과 이후 3개월 동안의 총 매출을 계산하세요.

• customers, orders, orderdetails 테이블을 사용하세요.
• 각 고객의 첫 주문일을 기준으로, 첫 주문 후 3개월 동안 발생한 매출 합계를 계산하세요.
• 출력: customerNumber, 첫 주문일, 3개월 내 총 매출

step1.각 고객의 최소 주문 일자와 최소 주문 일자의 3개월 뒤 일자를 출력

date_add(날짜 column, interval 3 month)을 활용하여 컬럼 출력

with first_purchase_table as 
(
select a.customerNumber, min(orderDate) as min_orderDate,date_add( min(orderDate),interval 3 month) as max_orderDate
from customers a inner join orders b on a.customerNumber = b.customerNumber 
inner join orderdetails c on b.orderNumber = c.orderNumber
group by a.customerNumber
),

select * from first_purchase_table

step2.각 고객 주문일자별 매출 구하기

new_table as 
(
select a.customerNumber,orderDate, (quantityOrdered * priceEach) as revenue
from customers a inner join orders b on a.customerNumber = b.customerNumber 
inner join orderdetails c on b.orderNumber = c.orderNumber
)

select * from new_table

step3.각 고객별 최소주문일자 및 3개월 간 주문 합계 구하기

step1과 step2을 조인한뒤, step2의 주문일자는 3개월 내에 주문한 일자여야하므로 where 조건 절을 활용해서 값을 구함.

select a.customerNumber,min_orderdate, sum(revenue) as sum_revenue

from first_purchase_table a join new_table b on a.customerNumber = b.customerNumber
where b.orderDate >= a.min_orderDate and b.orderDate <= a.max_orderDate 
group by customerNumber
order by 1;