[Tools]/SQL 이것저것

[MySQL] QCC 1회차

pjw250 2024. 10. 25. 19:59

DBeaver 환경에서 수강생들이 1시간 동안 주어진 문제를 푸는 방식으로 QCC 시험을 진행하였다.

총 2 문제이고 난이도는 중하? 정도 되는 듯 하였다.

 

"실제 입사 전형 중 SQL 코드 테스트와 최대한 유사하게 설계하였고 수강생들이 추후에 시험을 치룰 때 잘 적응 할 수 있도록 환경을 경험해 보는 것이 목적입니다."

 

일단, 문제에서 주어지는 사용될 테이블은 크게 4 가지

(1) Sales_SalesOrderHeader

(2) Sales_SalesOrderDetail

(3) Sales_Customer

(4) Person_Person

 

ERD는 아래와 같다.

 

[문제 1]

당신은 마케팅 팀의 일원으로서, 최근에 진행된 이메일 프로모션 캠페인에 관심이 있습니다. 해당 프로모션에 동의한 고객 수를 추산해야 합니다. 이메일 프로모션에 동의한 “개인(소매)” 고객의 수를 계산하여 출력하세요. 관련된 정보는 Person_Person 테이블에서 확인할 수 있습니다.

 

>> Person_Person 테이블에서 고객의 종류 칼럼 ['PersonType'] = IN (개인) 과 이메일 프로모션 동의 여부 ['EmailPromotion'] = 1 과 2 를 where 절로 걸면 끝남.

SELECT count(*)
FROM Person_Person pp
WHERE PersonType = 'IN' AND EmailPromotion IN('1','2');

 

[문제 2]

당신의 회사는 지난 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 회사 제품을 총 70개 이상 주문한 고객들을 찾아야 합니다. 고객 ID로 오름차순 정렬해주세요.

이 고객들의 주문 기록 Sales_SalesOrderHeader에 있으며, Sales_SalesOrderDetail 테이블에는 해당 주문에 대한 세부정보(주문 수량, 상품ID 등)를 확인할 수 있습니다. 주문 수량은 OrderQty 컬럼에서 확인할 수 있습니다. 고객의 기본 정보는 Sales_Customer에서 확인할 수 있으며, 사람에 대한 개인정보는 Person_Person 테이블에 저장되어 있습니다. 각 테이블에 중복이 없다고 가정하겠습니다.

 

>>> 총 4개의 테이블 각각의 연동된 PK/FK를 잘 찾아서 연관된 테이블 끼리 순차적으로 JOIN 하고 마지막에 total_quantity와 OrderDate 조건을 적용해서 sorting 시키면 된다.

>>> 먼저, 나는 with 구문으로 고객 정보를 담고 있는 두 개의 테이블을 합쳤다.

WITH Personal AS (SELECT Q.CustomerID, W.FirstName, W.LastName
				FROM (SELECT * FROM Sales_Customer) Q
				INNER JOIN (SELECT * FROM Person_Person) W
				ON Q.PersonID = W.BusinessEntityID)

 

>>> 이후 Sales_SalesOrderDetail 테이블과 Sales_SalesOrderHeader 테이블을 ['SalesOrderID'] 기준으로 묶었는다.

>>> 이때, Sales_SalesOrderHeader 에는 주문날짜 ['OrderDate']가 있고 Sales_SalesOrderDetail 에는 주문 수량 ['OrderQty']가 있으므로 JOIN 이후 group by ['CustomerID']로 sum(OrderQty) 한 뒤에 ['OrderDate'] 가 '2011-10' 인 row 들을 조건으로 걸었다.

SELECT B.CustomerID, B.OrderDate, sum(OrderQty) AS total_quantity
FROM 	(SELECT SalesOrderID, OrderQty FROM Sales_SalesOrderDetail) A
LEFT JOIN
		(SELECT SalesOrderID, CustomerID, OrderDate FROM Sales_SalesOrderHeader) B
ON A.SalesOrderID = B.SalesOrderID WHERE DATE_FORMAT(OrderDate, '%Y%m') = '201110'
GROUP BY CustomerID HAVING sum(OrderQty) >= 70

 

>>> 이후 위에 두개 테이블을 ['CustomerID'] 기준으로 합치면 완성

WITH Personal AS (SELECT Q.CustomerID, W.FirstName, W.LastName
				FROM (SELECT * FROM Sales_Customer) Q
				INNER JOIN (SELECT * FROM Person_Person) W
				ON Q.PersonID = W.BusinessEntityID)
SELECT C.CustomerID, D.FirstName, D.LastName, total_quantity
FROM (
		SELECT B.CustomerID, B.OrderDate, sum(OrderQty) AS total_quantity
		FROM 	(SELECT SalesOrderID, OrderQty FROM Sales_SalesOrderDetail) A
		LEFT JOIN
				(SELECT SalesOrderID, CustomerID, OrderDate FROM Sales_SalesOrderHeader) B
		ON A.SalesOrderID = B.SalesOrderID WHERE DATE_FORMAT(OrderDate, '%Y%m') = '201110'
		GROUP BY CustomerID HAVING sum(OrderQty) >= 70
	) C
LEFT JOIN
	(SELECT * FROM Personal) D
ON C.CustomerID = D.CustomerID ORDER BY 1 ASC;

 

 

>>> 아래엔 튜터님의 답

SELECT c.customerid as customer_id
	, p.firstname as first_name
	, p.lastname as last_name
	, SUM(so.orderqty) AS total_quantity
FROM adventureworks.Sales_Customer c
INNER JOIN adventureworks.Person_Person p ON c.personid = p.businessentityid
INNER JOIN adventureworks.Sales_SalesOrderHeader soh ON c.customerid = soh.customerid
INNER JOIN adventureworks.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
HAVING SUM(so.orderqty) >= 70    -- 총 주문량 70개 이상
ORDER BY c.customerid;            -- 고객 ID 오름차순 정렬