QCC | 3회차 코드 리뷰 & 피드백

☑️  문제1:  첫 주문 고객 연도별 매출 조회

[문제]

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

[문제 풀이]

내 코드

SELECT so.customerid
	  ,concat(c.firstname," ",c.lastname) AS customer_name 
	  ,year(so.orderdate) AS 'year'
	  ,round(sum(so.orderqty*so.unitprice),2) AS gmv
FROM sales_order so
	INNER JOIN customer c ON so.customerid = c.customerid 
WHERE so.customerid IN (SELECT customerid FROM sales_order GROUP BY customerid HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12' )
GROUP BY so.customerid,concat(c.firstname," ",c.lastname),year(so.orderdate)
ORDER BY so.customerid,'year';

 

이슈 및 해결 과정

• `핵심 포인트` : 2011-12월에 첫 주문 고객 필터링 
  • 아이디어 : where 절 서브쿼리로 첫 주문이 2011-12월인 고객만 필터링 하고 → 집계 해줌
• 문제 상황1 ) 고객별 orderdate의 최소값이 2011-12 일때만을 필터링 하기 위해, group by 후 having 절에서 필터링
  • HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12' 
  • 결과 ) orderdate 가 2011-12 이아닌 행들은 다 필터링 되서 날라가 버림 → 내가 필요한건, 2011-12월에 `첫` 구매한 `고객`의 데이터 전부가 필요함   
  • 해결 ) where 절 서브쿼리 사용 : 첫 주문이 2011-12월 인 고객id 만 선 필터링 한 후 집계에 사용 
• 문제 상황 2) select 절의 알리아스를 group by ~ having 절에서 사용 불가
  • select 절의 alias 를 group by~having 절에 사용할 수 있다고 생각해서 (?) 명칭으로 작성했더니, 둘의 결과값이 다름 → sql 실행 순서는 group by -> having -> select 임  
  • 해결 ) 기존 쿼리로 작성하여 해결 
• 개선사항 : date_format 과 min 집계값 같이 사용할때, 변환 -> 집계 보다 집계-> 변환 해야 성능이 좋음. 
  • 내 쿼리(변환 → 집계): HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12' 
  • 개선쿼리(집계 → 변환) : HAVING DATE_FORMAT(min(orderdate),'%Y-%m')='2011-12' 

한줄 피드백

• 해당 문제는 : PASS 
• MIN & DATE_FORMAT 사용시 : 집계 후 형 변환 적용 할것
• SELECT 절에서 명명한 이름은 ORDER BY / LIMIT 절 에서만 사용 가능함

---

☑️  문제 2 : 고객별 연평균 총 거래액 집계

[문제]

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

[문제 풀이]

내 코드

WITH year_sales AS (
	SELECT so.customerid
		  ,concat(c.firstname," ",c.lastname) AS customer_name 
		  ,year(so.orderdate) AS 'year'
		  ,round(sum(so.orderqty*so.unitprice),2) AS gmv
	FROM sales_order so
		INNER JOIN customer c ON so.customerid = c.customerid 
	WHERE so.customerid IN (SELECT customerid FROM sales_order GROUP BY customerid HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12' )
	GROUP BY so.customerid,concat(c.firstname," ",c.lastname),year(so.orderdate)
	ORDER BY so.customerid,'year'
)
SELECT customerid
	  ,customer_name
	  ,round(sum(gmv)/(max(year)-min(year)+1),2) AS avg_yearly_gmv
FROM year_sales
GROUP BY customerid ,customer_name
ORDER BY customerid;

 

이슈 및 해결 과정

• `핵심 포인트` : 평균 집계 시 없는 년도도 카운팅 필요 
  • 아이디어 : avg 함수 사용하지 않고, sum &고객별 집계 년도의 최대 -최소 값 이용하여 연산 후 평균 냄
  • 연평균 총 거래액 : (sum(gmv)/(max(year)-min(year)+1)
    • 최소년도~ 최대년도 사이값과 양끝값이 포함되야 하므로 +1 해줘야함

한줄 피드백

• 해당 문제 : PASS 
• 문제 테이블에서 년도 테이블이 있긴 했는데, 굳이 사용할 필요 없을것 같아 과감하게 무시

---

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

[문제]

• 2011년 12월에 첫 주문을 한 고객 대상으로, 고객별 각 연도에서의 주문 수량을 집계하고, 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도를 찾습니다.결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.
• 최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력합니다.

[문제 풀이]

내 코드

WITH year_sales AS (
	SELECT so.customerid
              ,concat(c.firstname," ",c.lastname) AS customer_name 
              ,year(so.orderdate) AS 'year'
              ,sum(so.orderqty) AS total_cnts
	FROM sales_order so
		INNER JOIN customer c ON so.customerid = c.customerid 
	WHERE so.customerid IN (SELECT customerid FROM sales_order GROUP BY customerid HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12'  )
	GROUP BY so.customerid,concat(c.firstname," ",c.lastname),year(so.orderdate)
	ORDER BY so.customerid,'year'
),max_year AS (
	SELECT *
       		,ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY total_cnts DESC, year DESC) AS rown
	FROM year_sales
)
SELECT customerid
      ,customer_name
      ,year AS max_qty_year
      ,total_cnts AS max_total_qty
FROM max_year
WHERE rown=1
ORDER BY customerid ;

 

 

개선 코드

WITH year_sales AS (
SELECT so.customerid
	  ,concat(c.firstname," ",c.lastname) AS customer_name 
	  ,year(so.orderdate) AS 'year'
	  ,sum(so.orderqty) AS total_cnts
	  ,ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY sum(so.orderqty) DESC, year(so.orderdate) DESC) AS rown
FROM sales_order so
	INNER JOIN customer c ON so.customerid = c.customerid 
WHERE so.customerid IN (SELECT customerid FROM sales_order GROUP BY customerid HAVING min(DATE_FORMAT(orderdate,'%Y-%m'))='2011-12'  )
GROUP BY so.customerid,concat(c.firstname," ",c.lastname),year(so.orderdate)
ORDER BY so.customerid,'year'
)
SELECT customerid
	  ,customer_name
	  ,year AS max_qty_year
	  ,total_cnts AS max_total_qty
FROM year_sales
WHERE rown=1
ORDER BY customerid ;

 

이슈 및 해결 과정

• `핵심 포인트` : 고객별 최대 주문 수량과 해당 년도 조회 ( 행 단위 필터링 ) 
  • 아이디어 : 가공 테이블에서 고객의 최대주문 수량 & 가장 최근 년도 순으로 순번 매긴 후 필터링 
  • 2번 쿼리에서 주문 수량 집계 이므로, select 절 sum(so.orderqty) 로 집계값 변경 
  • 순번 매기기 (row_number) 사용 : 윈도우 파라미터 order by 로 순번 설정 ( total_cnts DESC, year DESC) 
    • `왜?` 주문수량이 가장 많고, 가장 최근 년도이므로 → 중복값은 없음 (rank / dense_rank 사용해도 됨) 
  • 아우터 쿼리에서 1번만 필터링 해줌
• 개선 코드 해석
  • WITH 문 생성 후, 순번 매기는 쿼리 작성하려고 보니, 필터링 때문에 WITH 문 추가 생성 해줬음. BUT 첫번째 위드문에 같이 작성해도 무방 ( 굳이 WITH 문 2개 써줄 필요 없음)

한줄 피드백

• 해당 문제 : PASS 
• 순번 매기는 윈도우 함수 사용시 정렬조건 2개 이상 사용 가능 

 

---

QCC 리뷰 

• 문제를 제대로 읽는게 가장 중요함. 문제가 꽤나 길고 조건이 많아보여 문제 읽는데만 3-5분 정도 소요됐으나 이후 제대로 이해하고 풀수있었음
• 지금까지 계속 자잘한 실수가 많아서, qcc때 처음으로 쿼리 작성 후 샘플 아이디 뽑아서 검산 진행. 이 과정에서 1번 문제 having 절에서 제대로 필터링 안된걸 발견 할수있었음
• `정답`을 맞추는게 풀이 과정보다 중요하진 않지만, 자잘한 실수를 줄이기 위해 작성한 쿼리에서 샘플 추출후 검산해보는 작업은 좋은듯 
• 실제 코딩 테스트 진행할때도 , 문제정독 + 테이블 eda + 풀이 아이디어 + 쿼리 작성 + 검산 순으로  진행하면 실수를 줄일수있을듯