[SQL] 코멘토 프로젝트 - SQL을 활용해 다양한 지표 추출하기

 

Redash 쿼리 편집기 Northwind Database를 활용한 지표 추출하기

 

Northwind Database는 Northwind라는 가상의 식품회사에 대한 데이터베이스이다. 고객, 상품, 주문, 직원, 발주 등 총 20개의 테이블로 구성되어 있으며, 실제 기업의 데이터베이스와 유사한 구조로 되어있어 실무와 가까운 프로젝트를 하기에 적합하다.

---

문제1. 상품(product)의 카테고리(category)별로, 상품 수와 평균 가격대(list_price)를 찾는 쿼리를 작성하세요.

SELECT category, COUNT(*) AS product_count, AVG(list_price) AS average_price
FROM products
GROUP BY category

 

문제2. 2006년 1분기에 고객(customer)별 주문(order) 횟수, 주문한 상품(product)의 카테고리(category) 수, 총 주문 금액(quantity * unit_price)을 찾는 쿼리를 작성하세요.

SELECT o.customer_id, COUNT(DISTINCT o.id) AS order_count,
COUNT(DISTINCT p.category) AS category_count,
SUM(od. quantity * od.unit_price) AS total_order_amount
FROM orders o
JOIN order_details od ON o. id = od.order_id
JOIN products p ON od. product_id = p.id
WHERE o.order_date BETWEEN '2006-01-01' AND '2006-03-31'
GROUP BY o. customer_id

 

출력해야하는 값이 각각 다른 테이블에 속해있기 때문에 join을 여러번 시행해야하는 것이 주요 특징이다.

 

문제3. 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요.

SELECT status_name
FROM orders_status
WHERE id IN 
(SELECT status_id
FROM orders
WHERE order_date BETWEEN '2006-03-01' AND '2006-03-31')

 

where절 서브쿼리를 활용하는 것이 주요 특징이다.

 

문제4. 2006년 1분기 동안 세 번 이상 주문(order) 된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요.

SELECT product_id, COUNT (*) AS order_count
FROM order_details
WHERE order_id IN 
(SELECT id
FROM orders
WHERE order_date BETWEEN '2006-01-01' AND '2006-03-31')
GROUP BY product_id
HAVING COUNT(*) >= 3

 

마찬가지로 where절 서브쿼리를 사용해야하고, having절을 통해 주문건수가 3건 이상인 상품만 출력한다.

 

문제5-1. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요. 

SELECT DISTINCT e.id
FROM employees e
JOIN (SELECT employee_id
FROM orders
WHERE order_date BETWEEN '2006-01-01' AND '2006-03-31'
GROUP BY employee_id) AS Q1
ON e.id = Q1.employee_id
JOIN (SELECT employee_id
FROM orders
WHERE order_date BETWEEN '2006-04-01' AND '2006-06-30'
GROUP BY employee_id) AS Q2
ON e. id = Q2. employee_id

 

join을 2번 하여 1분기와 2분기 연속으로 주문을 받은 직원을 출력한다. join절에 서브쿼리를 사용하는게 독특한 문제였다.

 

5-2. 2006년 1분기, 2분기 연속으로 주문(order)을 받은 직원(employee)별로, 2006년 월별 주문 수를 찾는 쿼리를 작성하세요.

SELECT
    o.employee_id,
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
    COUNT(o.id) AS order_count
FROM orders o
WHERE o.employee_id IN (
        SELECT Q1.employee_id
        FROM (SELECT employee_id
            FROM orders
            WHERE order_date BETWEEN '2006-01-01' AND '2006-03-31'
            GROUP BY employee_id
            HAVING COUNT(id) > 0) AS Q1
         JOIN (SELECT employee_id
            FROM orders
            WHERE order_date BETWEEN '2006-04-01' AND '2006-06-30'
            GROUP BY employee_id
            HAVING COUNT(id) > 0) AS Q2
        ON Q1.employee_id = Q2.employee_id)
    AND o.order_date BETWEEN '2006-01-01' AND '2006-12-31'
GROUP BY o.employee_id, order_month

5-1 문제와 마찬가지로 join과 서브쿼리를 적절히 사용하는게 중요한 문제다.