주어진 자료
1번 문제
당신은 마케팅 팀의 일원으로서, 최근에 진행된 이메일 프로모션 캠페인에 관심이 있습니다. 해당 프로모션에 동의한 고객 수를 추산해야 합니다. 이메일 프로모션에 동의한 “개인(소매)” 고객의 수를 계산하여 출력하세요. 관련된 정보는 Person_Person 테이블에서 확인할 수 있습니다.
출력 값 예시
customer_count (고객수)
+----------------+
| customer_count |
+----------------+
| 8153 |
+----------------+나의 답안
SELECT COUNT(DISTINCT BusinessEntityID) AS customer_count
FROM Person_Person
WHERE (PersonType = 'IN')
AND (EmailPromotion <> 0)
;
답안
SELECT COUNT(DISTINCT BusinessEntityID) as customer_count
FROM adventureworks.Person_Person
WHERE EmailPromotion > 0 -- 0 = 이메일 프로모션을 받지 않음
AND PersonType = 'IN' -- IN = 개인(소매) 고객1번문제는 크게 어렵지 않게 풀 수 있었다.
2번 문제
당신의 회사는 지난 2011년 10월 동안 자사 제품을 많이 주문한 고객들에게 특별 할인 쿠폰을 제공할 예정입니다. 이를 위해 2011년 10월 한 달 동안 회사 제품을 총 70개 이상 주문한 고객들을 찾아야 합니다. 고객 ID로 오름차순 정렬해주세요.
이 고객들의 주문 기록 Sales_SalesOrderHeader에 있으며, Sales_SalesOrderDetail 테이블에는 해당 주문에 대한 세부정보(주문 수량, 상품ID 등)를 확인할 수 있습니다. 주문 수량은 OrderQty 컬럼에서 확인할 수 있습니다. 고객의 기본 정보는 Sales_Customer에서 확인할 수 있으며, 사람에 대한 개인정보는 Person_Person 테이블에 저장되어 있습니다. 각 테이블에 중복이 없다고 가정하겠습니다.
출력 값 예시
+-------------+------------+-------------+----------------+
| customer_id | first_name | last_name | total_quantity |
+-------------+------------+-------------+----------------+
| 29486 | Kim | Abercrombie | 92 |
| 29510 | Cecil | Allison | 115 |
| 29525 | Teresa | Atkinson | 103 |
...
+-------------+------------+-------------+----------------+결론적으로 시간이 모자라 풀지 못했다.
2번문제를 복잡하게 생각해서 WITH구문으로 조인을 각각 해서 또 밑에서 조인하려고 했었는데, 의도는 아래와 같이 풀고 싶었다.
1번 답안
WITH A AS(SELECT H.CustomerID CustomerID, sum(D.OrderQty) total_quantity
FROM Sales_SalesOrderHeader H
JOIN Sales_SalesOrderDetail D
ON H.SalesOrderID = D.SalesOrderID
WHERE H.OrderDate LIKE '2011-10%' # 2011-10 주문
GROUP BY 1
HAVING total_quantity >=70) # 총주문량 70개 이상
,
B AS(SELECT CustomerID, FirstName, LastName
FROM Sales_Customer C
JOIN Person_Person P
ON C.PersonID = P.BusinessEntityID)
SELECT A.CustomerID, FirstName, LastName, total_quantity
FROM A
JOIN B
ON A.CustomerID = B.CustomerID
ORDER BY 1 #고객 아이디 기준 오름차순
; 시험 끝나고 살펴보니 그냥 차분하게 조인만 하면 되는 문제였다. 허무하고 분했지만 이게 내 실력이라는걸 인정하게 됐다. 차분하게 조인만 이용해 풀어보았다.
2번 답안
SELECT H.CustomerID,
P.FirstName,
P.LastName,
SUM(D.OrderQty) as total_quantity
FROM Sales_SalesOrderHeader H
JOIN Sales_SalesOrderDetail D
ON H.SalesOrderID = D.SalesOrderID
JOIN Sales_Customer C
ON H.CustomerID = C.CustomerID
JOIN Person_Person P
ON C.PersonID = P.BusinessEntityID
WHERE H.OrderDate LIKE '2011-10%' # 2011-10 주문
GROUP BY 1
HAVING total_quantity >=70 # 총주문량 70개 이상
ORDER BY 1 #고객 아이디 기준 오름차순
;WHERE절에 조건을 다양하게 설정 할 수 있기 때문에 튜터님 답안으로도 적용해 보았다.
튜터님 답안
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 오름차순 정렬예정된 쿼리테스트를 봤다. 테스트 자체가 너무 오랜만이라 긴장이 돼서 점심도 못 먹었다😂 솔직히 다 끝나고 난 뒤 차분히 보니 못 푸는게 이상한 문제들이었는데 어이없게 어렵게 접근해 시간을 다 보냈다. 이것도 실력이라고 생각한다. 결국 본질을 놓치고 기본이 부족하기 때문에 일어나는 일 같다. 어려운 문제를 푸는 것도 중요하지만 차근차근 기본을 닦는게 제일 필요한 일이라는 것을 깨닫는 소중한 시간이었다. 오늘 푼 문제를 정리하고, 다음부터는 아는 것은 절대 틀리지 않도록 내실을 다져야겠다.
