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

☑️  문제 1. 지역별 매출이 가장 높은 매장의 지역 조회

[문제]

• 지역별로 매출이 가장 높은 매장의 매출을 조회하는 SQL 문을 작성해주세요
• 단, 해당 지역에 매장이 두 개 이상인 경우만 결과에 포함해주세요. 결과는 지역 이름을 기준으로 오름차순으로 정렬해주세요.

---

[문제 풀이]

내 코드

SELECT REGION_NAME AS region_name
	  ,MAX(SALES) AS highest_sales
FROM stores 
WHERE REGION_NAME IN (SELECT REGION_NAME 
					FROM stores s 
					GROUP BY REGION_NAME
					HAVING COUNT(DISTINCT STORE_ID) >=2)
GROUP BY REGION_NAME
ORDER BY REGION_NAME;

 

수정 코드 

SELECT REGION_NAME AS region_name
	  ,MAX(SALES) AS highest_sales
FROM stores 
GROUP BY REGION_NAME
HAVING COUNT(DISTINCT STORE_ID)>1
ORDER BY REGION_NAME;

 

이슈 및 해결 과정

• WHERE 절 서브쿼리로 지역별 매장이 2개 이상인 경우 필터링 후 가장 높은 매출 출력
• 개선점 
  • 불필요한 서브쿼리 진행함 → 그룹바이 된 결과값으로 필터링 하면 되니까 HAVING 사용했어야 함
  • WHERE 절 서브쿼리에도 GROUP BY 들어가므로 어차피 중복

한줄 포인트

• 결과 : PASS 
• WHERE 절 서브쿼리 GROUP BY 쿼리가 아우터 쿼리랑 중복될 경우 HAVING 절로 필터링 할수있지 않나 점검 해보기 (불필요한 서브쿼리 생성을 방지 하자)

☑️  문제 2. 이상 주문 데이터 사용자 수 집계

[문제] 

• 첫 번째 결제일보다 이전에 상품을 주문한 사용자최근 특정 사용자들이 결제를 하지 않고 상품을 주문하거나, 결제를 하지 않은 시점에 이미 상품을 주문하는 버그가 발견되었습니다. 🐞 해당 버그를 악용한 사용자를 파악하기 위해 SQL 문을 작성해주세요. 다음 조건에 해당되는 사용자 수를 출력해주세요 :
  • 결제를 하지 않고 상품을 주문한 사용자
  • 첫 번째 결제일보다 이전에 상품을 주문한 사용자

 

---

[문제 풀이]

내 코드

WITH payments2 AS ( -- 고객의 첫 결제일
	SELECT USER_ID
		  ,min(PAY_DATE) AS fst_date
	FROM payments p 
	GROUP BY USER_ID
)
SELECT COUNT(DISTINCT o.USER_ID) AS cnt
FROM orders o 
	LEFT JOIN payments2 p ON o.USER_ID = p.USER_ID
WHERE p.USER_ID IS NULL
   OR p.fst_date > o.ORDER_DATE;

 

이슈 및 해결 과정

• 고객의 첫 결제일 CTE 생성 LEFT JOIN 하여 결제 내역이 없는 주문 고객 같이 연결
• WHERE 절에서 첫 결제일보다 먼저 주문 내역이 있는 경우만 필터링 & 결제 내역이 없는 주문 고객 필터링
• 필터링한 고객 집계

한줄 포인트

• 결과 :PASS
• 쿼리 작성은 어렵지 않았으나, 문제 조건을 이해하는데 5-7분 정도 소요 → 주어진 예시와 결과값을 집중해서 확인할것 (한글이 가장 어렵다 ..)

☑️  문제3. 고객의 장바구니 분석(연관제품 파악)

[문제]

• 데이터 분석팀은 고객이 특정 상품 X를 구매했을 때 상품 Y도 함께 구매할 확률을 분석하고자 합니다. 이를 위해, 쇼핑 카트 데이터에서 서로 다른 두 제품 X와 Y가 같은 주문(CART_ID)에 포함된 주문 수를 확인하려고 합니다.
  • 제품 X와 Y가 같은 주문에 포함된 경우를 계산합니다.
  • 두 제품은 서로 다른 이름이어야 하며, 한 쌍의 경우(예: Coffee와 Sausages)는 다른 순서(예: Sausages와 Coffee)로도 포함됩니다.
  • 결과는 각 제품 쌍과 해당 제품이 함께 포함된 주문 수를 반환해야 합니다.
  • 제품 이름 X와 Y를 기준으로 알파벳 순으로 오름차순 정렬합니다.

---

[문제 풀이]

내 코드

SELECT c1.name AS name_x
    ,c2.name AS name_y
    ,COUNT(DISTINCT c1.cart_id) AS orders
FROM cart_products c1
	INNER JOIN cart_products c2 ON c1.cart_id=c2.CART_ID AND c1.NAME <> c2.NAME
GROUP BY c1.name,c2.name
ORDER BY name_x,name_y

 

이슈 및 해결 과정

• 아이디어 :self join 으로 고객이 구매한 제품 쌍 조회후 구매 건수 집계
  • 조건에 x,y는 서로 다르고 쌍의 순서가 있으므로 (ex. bread-coffee 쌍과 coffee-bread쌍 각각 집계) self join 후 제품명이 같이 않을때만 on 조건으로 걸어줌 (name이 같으면 coffee-coffee 도 연결되니까) 
  • 각 쌍을 그룹바이 해준뒤 구매 건수 집계
•  추가 풀이 
  • 만약, 중복 쌍을 제거해야한다면(bread-coffee를 하나로 집계) on 조건에 `AND c1.NAME < c2.NAME` 하면 해결 
• 피드백 
  • 역대급 뻘짓한 문제였음,카티션 곱으로 NAME 연결한 CTE 만들고, 전체 테이블에서 조건을 걸어주려고 하였으나, 아래 까지만 하고 진행이 안됨 → SELF JOIN으로 간단하게 풀수있는 문제였음.. 
  • 코드카타때 GROUP_CONCAT 함수 & RECURSIVE 사용한 문제를 풀다보니 계속 어렵게 접근하게 됐던게 패착이었던것 같다. 
  • 장바구니 분석은 SELF JOIN 적극 활용하자

WITH sub AS (
	SELECT *,concat(name_x,',',name_y) AS conct
	FROM (SELECT NAME AS name_x FROM cart_products GROUP BY NAME) AS items1
	   , (SELECT NAME AS name_y FROM cart_products GROUP BY NAME) AS items2
	WHERE name_x<>name_y 
)
SELECT name_x,name_y,conct
-- - cocnt 이 그룹값에 포함되면 1 안되면 0 -> 근데 순서도 맞아야하고.. 
FROM sub;

 

한줄 포인트

• 결과 :FAIL
• 장바구니 분석시, 어렵게 접근하지 말고 SELF JOIN 사용
• 만약, 제품 순서에 관계 없이 (중복제거) 하여 출력한다면 ON 조건에 A<B 로 처리해주자