3차 QCC review

≣ 목차

 

✏️ 1번 - 첫 주문 고객 연도별 매출 조회

문제

“2011년 12월”에 첫 주문을 한 고객들의 연도별 매출을 조회

조건)고객ID 기준 오름차순, 연도 기준 오름차순 정렬

 

출력 결과

• 고객 ID (`customerid`)
• 고객 이름 (`customer_name`) -> firstname, lastname 결합해서 만들기
• 연도 (`year`)
• 총 거래액 = GMV (`gmv`) -> unitprice * orderqty

 

풀이

with sub as (
SELECT c.customerid,
		concat(firstname, ' ', lastname) customer_name
from customer c join sales_order so on c.customerid = so.customerid
group by 1,2
having date_format(min(so.orderdate), '%Y-%m') = '2011-12'
) -- 2011/12 첫주문 한 고객 id,name 필터링 
SELECT s.customerid,
		s.customer_name,
		year(so.orderdate) year,
		sum(unitprice*orderqty) GMV
from sub s left join sales_order so on s.customerid=so.customerid 
group by 1,2,3
order by 1,3;

 

1. with sub절 : `2011/12`에 첫 주문을 한 `고객 id`, `name`을 필터링

• 12월에 '첫 주문'을 한 고객이니까 min(so.orderdate)
• group by 해놓은 고객 정보에서 날짜 조건(12월 첫 주문) 걸어야 하니까 having

2. sub, sales_order 조인

• `customerid`, `customer_name`, `year`, `GMV` 출력
• 집계함수 빼고 group by 1,2,3 
• order by customerid, year

 

---

✏️ 2번 - 고객별 연평균 총 거래액 집계

문제

2011년 12월에 첫 주문을 한 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로,

해당 기간 동안의 **연평균 총거래액(GMV)**을 계산(소수점 2째자리까지)

조건) 결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬

 

출력 결과

• 고객 ID (`customerid`)
• 고객 이름 (`customer_name`) -> firstname, lastname 결합해서 만들기
• 연평균 매출 (`avg_yearly_gmv`) -> 소수점 둘째자리

 

풀이

with sub as(
SELECT c.customerid,
		concat(firstname, ' ', lastname) customer_name
from customer c join sales_order so on c.customerid = so.customerid
group by 1,2
having date_format(min(so.orderdate), '%Y-%m') = '2011-12'
), -- 2011/12 첫주문 한 고객 id,name 필터링 
sub2 as (
SELECT s.customerid,
		s.customer_name,
		year(so.orderdate) year, -- 주문연도만 뽑기 
		sum(so.unitprice*so.orderqty) gmv -- 고객의 연도별 GMV 
from sub s left join sales_order so on s.customerid = so.customerid
group by 1,2,3
)
SELECT customerid,
		customer_name,
		round(sum(gmv) / (max(year) - min(year) +1), 2) avg_yearly_gmv 
from sub2
group by 1,2
order by 1;

 

1. with sub절 : `2011/12`에 첫 주문을 한 `고객 id`, `name`을 필터링

•  1번 문제와 동일한 쿼리

2. sub2절 : 각 고객별 주문 `year`, 각 고객의 연도별 `gmv` 를 필터링 

•  12월에 첫 주문을 한 고객 리스트 뽑고,
•  각 `고객별 주문 연도`와 `연도별 gmv`를 출력한다.
•  한 사람당 첫주문, 마지막 주문, 연도별 gmv 알 수 있음.

3. 마지막 쿼리에서 : 연평균 gmv 계산

•  `sum(gmv)` 를 `max(year) - min(year)+1`, 즉 기간으로 나눠줌 = 연평균 gmv

---

✏️ 3번 - 고객별 최대 주문 수량 연도와 주문 수량 집계

문제

2011년 12월에 첫 주문을 한 고객 대상으로

1. 고객별 각 연도에서의 주문 수량을 집계하고,

2. 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도 찾기

조건) a. 최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력

         b. 고객 ID 기준으로 오름차순으로 정렬

 

출력 결과

• 고객 ID (`customerid`)
• 고객 이름 (`customer_name`) -> firstname, lastname 결합해서 만들기
• 최대 주문 수량 발생 연도 (`max_qty_year`)
• 최대 주문 수량 (`max_total_qty`)

 

풀이

with sub as(
SELECT c.customerid,
concat(firstname, ' ', lastname) customer_name
from customer c join sales_order so on c.customerid = so.customerid
group by 1,2
having date_format(min(so.orderdate), '%Y-%m') = '2011-12'
), -- 2011/12 첫주문 한 고객 id,name 필터링 
sub2 as (
SELECT s.customerid,
s.customer_name,
year(so.orderdate) year, -- 주문발생연도
sum(so.orderqty) total_qty, -- 총 주문수량
rank() over (partition by s.customerid order by sum(so.orderqty) desc, year(so.orderdate) desc) as qty_rank  
from sub s left join sales_order so on s.customerid = so.customerid 
group by 1,2,3
) 
SELECT customerid,
		customer_name,
		year as max_qty_year,
		total_qty as max_total_qty
from sub2
where qty_rank = 1 --주문수량이 최대인 경우 필터링
order by 1;

 

1. with sub절 : `2011/12`에 첫 주문을 한 `고객 id`, `name`을 필터링

• 1번 문제와 동일한 쿼리

2. sub2절 : 고객정보(id, name), 주문발생연도, 총 주문수량 필터링 + rank()로 주문수량 순위 매기기

• 고객 정보, 고객별 주문 발생시킨 연도 출력 -> year(so.orderdate) year
• 고객별 총 주문 수량 출력 -> sum(so.orderqty) total_qty
• 각 고객별 주문 발생 연도와 연도별 총 주문수량 확인.
• 이제 total_qty 별로 순위를 매겨서 최대 주문수량이 발생한 연도 확인해야함.
• rank() ~  -> 고객id 별 총 주문수량 순위 매기기, 이때 총 주문수량이 같다면 최근연도 출력하기 (year desc)

고객별 주문 발생연도, 총 주문수량, 주문수량 순위 알 수 있음.

 

  3. 마지막 쿼리에서 : `최대 주문이 발생한 year`와 `total_qty`을 집계

• where qty_rankk = 1 -> 주문수량이 최대인 경우만 필터링