QCC 1회차

https://query-test-mu.vercel.app/?set=1&problem=7

문제가 비공개로 바꼈다..

1번

SELECT COUNT(DISTINCT business_entity_id) as customer_count
FROM qcc.person
WHERE email_promotion > 0 -- 0 = 이메일 프로모션을 받지 않음
AND person_type = 'IN' -- IN = 개인(소매) 고객

2번

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