QCC 2회차

📍 고유수면 무조건 distinct 붙이는 습관

1번

내가 쓴 답 : 

# 이메일 프로모션에 “동의”한 고객
# 해당 고객들 중 "개인(소매)" 고객의 수
SELECT
	COUNT(*) AS customer_count
FROM
	Person_Person
WHERE
	EmailPromotion != 0 AND PersonType = 'IN';

정답 : 

SELECT COUNT(DISTINCT BusinessEntityID) as customer_count
FROM Person_Person
WHERE EmailPromotion > 0 -- 0 = 이메일 프로모션을 받지 않음
AND PersonType = 'IN' -- IN = 개인(소매) 고객

💡 고유수 구할 때는 무조건 distinct 붙이는 습관! 기억하기

---

2번

내가 (뒤늦게) 쓴 답 :

WITH a AS(
	SELECT 
		sh.CustomerID,
		SUM(sd.OrderQty) AS total_quantity,
		CASE WHEN SUM(sd.OrderQty) >= 100 THEN 'VIP'
	 	 WHEN SUM(sd.OrderQty) >= 70 THEN 'GOLD'
	 	 WHEN SUM(sd.OrderQty) >= 40 THEN 'SIVER'
		 WHEN SUM(sd.OrderQty) >= 20 THEN 'BRONZE'
	 	 ELSE 'BASIC' END customer_class
	FROM
		Sales_SalesOrderDetail sd 
        JOIN Sales_SalesOrderHeader sh 
        	ON sd.SalesOrderID = sh.SalesOrderID
	WHERE DATE_FORMAT(OrderDate,'%Y-%m') = '2011-10'
	GROUP BY 1)
SELECT
	a.CustomerID AS customer_id,
	pp.FirstName AS first_name,
	pp.LastName AS last_name,
	a.total_quantity,
	a.customer_class
FROM
	a JOIN Sales_Customer sc ON a.CustomerID = sc.CustomerID
	  JOIN Person_Person pp ON sc.PersonID = pp.BusinessEntityID
ORDER BY a.total_quantity DESC ;

- 1) 2011년 10월 중 고객 아이디로 그룹, 고객 등급을 분류한 후 with로 처리
- 2) 고객 정보 조회

정답 : 

SELECT c.customerid as customer_id
	, p.firstname as first_name
	, p.lastname as last_name
	, SUM(so.orderqty) AS total_quantity
	, case when SUM(so.orderqty) >= 100 then 'VIP'
	       when SUM(so.orderqty) >= 70 then 'GOLD'
	       when SUM(so.orderqty) >= 40 then 'SILVER'
	       when SUM(so.orderqty) >= 20 then 'BRONZE'
	       else 'BASIC' end as customer_class
FROM Sales_Customer c
INNER JOIN Person_Person p ON c.personid = p.businessentityid
INNER JOIN Sales_SalesOrderHeader soh ON c.customerid = soh.customerid
INNER JOIN Sales_SalesOrderDetail so ON soh.salesorderid = so.salesorderid
WHERE DATE(orderdate) BETWEEN '2011-10-01' AND '2011-10-31' -- 2011-10 주문 
GROUP BY c.customerid, p.firstname, p.lastname
ORDER BY total_quantity desc;