[SQL/QCC] 실전 연습 2차 - 코딩 테스트 2문제

문제1.

 

>> 출력 결과:

>> 내 작성 쿼리문 (정답)

SELECT COUNT(distinct business_entity_id) as customer_count
FROM person
WHERE email_promotion != 0
  AND person_type = 'IN';

>> 🔍 해설 쿼리 : 여러가지 풀이!!

SELECT
FROM person
WHERE email_promotion IN (1, 2)
  AND person_type = 'IN'
  
>>> WHERE 절 대체 가능:
✅ email_promotion > 0
✅ email_promotion BETWEEN 1 AND 2

---

문제2.

🚨 아래 사진처럼 테이블 간의 관계도(?)를 (머리속에) 그려보면 이해하기가 수월해짐!! 이게 ERD, 엔티티 관계도 라는 거군 ㅎㅎ

 

>> 출력 결과:

>> 내 작성 쿼리문 (정답) : CTE 구문 2개 사용

-- 2011년 10월 동안 제품을 많이 구매한 고객에게 특별 할인쿠폰 제공 예정
-- 2011년 10월 한달 동안 구매한 회사 제품 구매 수량이 총 70개 이상인 고객 출력
-- 취소 주문은 제외
-- 고객id, 이름, 성, 총 주문 수량
-- 고객id  오름차순


WITH cond as (
select h.customer_id, sum(d.order_qty) as total_quantity
from sales_order_detail d
join sales_order_header h on d.sales_order_id=h.sales_order_id
where h.order_date like '2011-10%'
  and h.status != 6
group by h.customer_id
having sum(d.order_qty) >= 70
), sp as (
  select c.customer_id,
         p.first_name,
         p.last_name
  from sales_customer c
  join person p on c.person_id=p.business_entity_id
  )
select c.customer_id,
       cp.first_name,
       cp.last_name,
       c.total_quantity
from cond as c
join sp as cp on c.customer_id=cp.customer_id
order by c.customer_id;

>> 🔍 해설 쿼리 : 다른 풀이!! 다중 JOIN 사용

-- 한달 (10월), 제품 수량 70 이상, 고객
-- 취소된 주문은 제외
-- 고객id, 성, 이름, 총 주문량
-- 고객id 오름차순

SELECT c.customer_id as customer_id
	, p.first_name as first_name
	, p.last_name as last_name
	, SUM(so.order_qty) AS total_quantity
FROM qcc.sales_customer c
INNER JOIN qcc.person p ON c.person_id = p.business_entity_id
INNER JOIN qcc.sales_order_header soh ON c.customer_id = soh.customer_id
INNER JOIN qcc.sales_order_detail so ON soh.sales_order_id = so.sales_order_id
WHERE DATE(order_date) BETWEEN '2011-10-01' AND '2011-10-31' -- 2011-10 주문
AND soh.status <> 6 -- 취소 비포함
GROUP BY c.customer_id, p.first_name, p.last_name
HAVING SUM(so.order_qty) >= 70    -- 총 주문 수량 70개 이상
ORDER BY c.customer_id            -- 고객 ID 오름차순 정렬

>> 도출 순서

1. 우선 고객 정보를 뽑아봄.

SELECT sc.customer_id, p.first_name, p.last_name
FROM sales_customer sc
JOIN person p ON sc.person_id=p.business_entity_id;

2. 주어진 조건들을 뽑아보자 : 기간, 총 주문량, 취소건 제외

SELECT customer_id, sum(order_qty) as order_qty
FROM sales_order_header soh
JOIN sales_order_detail sod ON soh.sales_order_id=sod.sales_order_id
WHERE date_format(order_date, '%Y-%m') = '2011-10'
GROUP BY customer_id
HAVING sum(order_qty) >= 70

-- WHERE order_date BETWEEN '2011-10-01' AND '2011-10-31' ❌
-- WHERE order_date BETWEEN '2011-10-01' AND '2011-11-01' ✅
>>> 🚨 시분초 데이터까지 있을 때,
    BETWEEN으로 하면 : 2011-10-01 00:00:00 부터 2011-10-31 00:00:00 까지로 읽혀서
    10-31 하루가 빠지게 됨...!!! 따라서, 2011-11-01 까지로 설정

>>> 🚨 날짜컬럼 LIKE '2011-10%' 이거는 해외에서 날짜형식이 다를 수 있으니 사용 지양(?)하자!

3. 위 두개를 각각 결과를 출력해보고, 맞을 경우 JOIN 시작!

SELECT sc.customer_id, 
        p.first_name,
        p.last_name,
        sum(sod.order_qty) as order_qty
FROM sales_order_header soh
JOIN sales_order_detail sod ON soh.sales_order_id=sod.sales_order_id
JOIN sales_customer sc      ON sc.customer_id=soh.customer_id
JOIN person p               ON sc.person_id=p.business_entity_id
WHERE date_format(soh.order_date, '%Y-%m') = '2011-10'
-- WHERE date(soh.order_date) >= '2011-10-01' AND date(soh.order_date) < '2011-11-01'
  AND soh.status <> 6
GROUP BY sc.customer_id    
HAVING sum(sod.order_qty) >= 70
ORDER BY sc.customer_id;

>>>🚨 Q)집계함수가 있는데, GROUP BY에 customer_id만 들어가도 되나요?
      A)이미 집계를 customer_id별로 진행을 했기 때문에 괜찮다! 불안하면 다 넣어도 된다!

✅ 주의할 팁 한가지!!! 
쿼리문을 끝내고 세미콜론 ; 하고나서 다시 뒤에 주석을 넣으면 결과에서 오류가 나올 수 있으니, 쿼리문 뒤 주석은 지우도록 하자!

 

끝.