테이블 설명

• 꼭 읽어야 함!
• 실제 테스트에서는 pk 관계 안 보여줌
  • 보통 첫 번째가 pk임
• 엔티티 관계도 반드시 확인!!
  • 엔티티만 보고도 쿼리 짤 수 있어야 함

Q1

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

Point

“개인(소매)” 고객 → IN
이메일 프로모션 → emailpromotion column 1, 2

• count, where 활용

-- Q1
SELECT
  COUNT(*) AS "이메일 프로모션 동의한 개인 소매 고객 수"
FROM 
  Person_Person 
WHERE 
  PersonType = 'IN'
  AND EmailPromotion IN(1,2)
;

튜터님 답

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

Q2

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

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

Point

2011년 10월 한 달 동안 → BETWEEN '2011-10-01' AND '2011-10-31'
주문 수량 70개 이상 → 고객 id로 groupby, sum(주문 수량)

• 문제를 못 풀었어도 뭐라도 적고 제출하는 게 더 중요함
  • 무조건 기록 남겨두기!
  • 쓰다 만 코드라도 무조건 제출하시오

• 검증은 아래처럼
  

• 이렇게 해도 됨
  

• HAVING!!!!
  • having 쓰는 습관~
    

• Q. Status,OnlineOrderFlag는 고려안해도 되나요?
  • A. 문제에서 요구하지 않았으니 고려하지 마십시오.

근데 OnlineOrderFlag에 영업사원과 고객으로 나눈 표현을 썼는데 영업사원도 고객으로 포함시켜야하는걸까요?

identation 은 고려 해야될까요..

저는 2번을 with문 써서 풀었는데 다중조인이랑 with절이랑 어떤 쿼리가 더 효율적인가요?

• 온라인 쿼리 테스트(라이브 아닌 경우)에서는 효율까지는 생각하지 않아도 됩니다.
• 정확한 답이 나오는 게 더 중요!

어디서는 Join을 써야하고 어디서는 left, join을 사용해야할지 코딩테스트에서 쉽게 판단하는법? 이 뭐가 있을까요?

이렇게 여러 테이블에서 가져와서 할때 한번에 조인하고 한번에 처리하는게 좋을까요? 아니면 처리 조인 처리 조인 하는게 좋을까요?

처리 조인 처리 조인은
조건 1개를 맞추려고 조인
조건 2개를 맞추고 조인
그걸 최종 서브쿼리로 조인인 느낌

SELECT ab.customerID customer_id, Firstname first_name, LastName last_name, total_quantity
from (
select customerID, sum(OrderQty) total_quantity
from adventureworks.Sales_SalesOrderHeader a
join adventureworks.Sales_SalesOrderDetail b
on a.SalesOrderID = b.SalesOrderID and date_format(OrderDate, '%Y-%m') = '2011-10'
group by 1
having sum(OrderQty) >= 70
) ab
join (
select customerID, FirstName, LastName
from adventureworks.Sales_Customer c
join adventureworks.Person_Person d
on c.personID=d.BusinessEntityID
) cd
on ab.customerID = cd.customerID
order by 1

OnlineOrderFlag =1 설정 때문이었습니다.

• 테이블 관계성 파악을 잘 하는 게 중요해요!

• 주석을 잘 답시다!

• 예시와 동일하게 값이 나올 필요는 없음

  • "형식"만 보세요

튜터님 답

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 오름차순 정렬