SQL | QCC 3회차

1번 : 첫 주문 고객 연도별 매출 조회

“2011년 12월”에 첫 주문을 한 고객들의 연도별 매출을 조회하는 SQL 문을 작성하세요. 고객 이름은 이름과 성을 결합하여 생성합니다. 결과는 고객 ID(customerid)기준 오름차순, 연도(year) 기준 오름차순으로 정렬합니다.

- 2011년 12월에 첫 주문을 한 고객 → 고객별로 그룹화, 주문날짜 최솟값이 2011년 12월인 고객 필터링

- 고객 이름 CONCAT 함수로 합치기

- 매출(gmv) = unitprice * orderqty

1) 2011년 12월에 첫 주문한 고객 필터링

SELECT
	c.customerid,
	CONCAT(c.firstname, ' ', c.lastname) AS customer_name	# 고객 성, 이름 합치기
FROM 
	customer c JOIN sales_order s ON c.customerid = s.customerid
GROUP BY 1							# 고객별 그룹화
HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12'	# 주문날짜 최솟값(첫 구매)이 2011년 12월인 고객만 필터링

2) 고객 연도별 매출 조회

WITH a AS
	(SELECT
		c.customerid,
		CONCAT(c.firstname, ' ', c.lastname) AS customer_name
	FROM 
		customer c JOIN sales_order s ON c.customerid = s.customerid
	GROUP BY 1
	HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12')
SELECT 
	a.customerid,
	a.customer_name,
	YEAR(s.orderdate) AS year,
	SUM(s.unitprice * s.orderqty) AS gmv
FROM 
	a LEFT JOIN sales_order s ON a.customerid = s.customerid 
GROUP BY 1, 3
ORDER BY 1, 3 ;

---

2번 : 고객별 연평균 총 거래액 집계

2011년 12월에 첫 주문을 한 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 해당 기간 동안의 연평균 총거래액(GMV)을 계산합니다. 소수점 2째자리까지 계산해주세요.

결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

- 연평균 총거래액 = SUM(gmv) / MAX(year) - MIN(year) +1

WITH a AS
	(SELECT
		c.customerid,
		CONCAT(c.firstname, ' ', c.lastname) AS customer_name
	FROM 
		customer c JOIN sales_order s ON c.customerid = s.customerid
	GROUP BY 1
	HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12'),
b AS 
	(SELECT 
		a.customerid,
		a.customer_name,
		YEAR(s.orderdate) AS year,
		SUM(s.unitprice * s.orderqty) AS gmv
	FROM 
		a LEFT JOIN sales_order s ON a.customerid = s.customerid 
	GROUP BY 1, 3
	ORDER BY 1, 3)
SELECT
	customerid,
	customer_name,
	ROUND(SUM(gmv) /(MAX(year) - MIN(year)+1),2) AS avg_yearly_gmv
FROM 
	b
GROUP BY 1,2
ORDER BY 1 ;

---

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

2011년 12월에 첫 주문을 한 고객 대상으로, 고객별 각 연도에서의 주문 수량을 집계하고, 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도를 찾습니다.

최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력합니다.

결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

1) RANK 함수 이용 주문 수량이 많은 순서, 가장 최근 연도로 순위 매김

WITH a AS (
	SELECT
		c.customerid,
		CONCAT(c.firstname, ' ', c.lastname) AS customer_name
	FROM 
		customer c JOIN sales_order s ON c.customerid = s.customerid
	GROUP BY 1
	HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12')
SELECT 
	a.customerid,
	a.customer_name,
	YEAR(s.orderdate) AS year,
	SUM(s.orderqty) AS total_qty,
	RANK() OVER(PARTITION BY a.customerid ORDER BY SUM(s.orderqty) DESC, YEAR(s.orderdate) DESC) AS rnk
FROM 
	a LEFT JOIN sales_order s ON a.customerid = s.customerid
GROUP BY 1, 3

2) 최대 주문 수량 (rnk = 1) 만 조회

WITH a AS (
	SELECT
		c.customerid,
		CONCAT(c.firstname, ' ', c.lastname) AS customer_name
	FROM 
		customer c JOIN sales_order s ON c.customerid = s.customerid
	GROUP BY 1
	HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12'),
b AS(
	SELECT 
		a.customerid,
		a.customer_name,
		YEAR(s.orderdate) AS year,
		SUM(s.orderqty) AS total_qty,
		RANK() OVER(PARTITION BY a.customerid ORDER BY SUM(s.orderqty) DESC, YEAR(s.orderdate) DESC) AS rnk
	FROM 
		a LEFT JOIN sales_order s ON a.customerid = s.customerid
	GROUP BY 1, 3)
SELECT 
	b.customerid,
	b.customer_name,
	b.year AS max_qty_year,
	b.total_qty  AS max_total_qty 
FROM b
WHERE rnk = 1
ORDER BY 1 ;