[2주차 - 과제] SQL 입문부터 활용까지 - 데이터 분석 보고서 작성과 대시 보드 개발

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

select category ,count(product_code) ‘상품’, avg(list_price) ‘평균가격’ 
from products
group by category
;

---

/* 멘토 피드백 
COUNT (1) 은 널값을 포함한 개수
COUNT(컬럼명) 은 널값을 포함하지 않은 개수
*/

/* 멘토 예시 답안*/
select category, count(1) cnt, avg(list_price) avg_price
from products
group by category;

 

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

select c.id as customer_id,
       count(distinct o.id) as order_num,
       count(distinct p.category) as category_num,
       sum(od.quantity * od.unit_price) as total_price
from customers c,
     orders o,
     order_details od,
     products p
where c.id = o.customer_id
 and o.id = od.order_id
 and p.id = od.product_id
 and paid_date between '20060101' and '20060331'
group by c.id
;

---

/* 멘토 피드백
FROM 에서 , 는 INNER JOIN과 같다
FROM 에서 customers 테이블은 굳이 가져오지 않아도 되는 부분이다
*/

/* 멘토 예시 답안 */
select o.customer_id, 
    count(distinct o.id) order_cnt, 
    count(distinct p.category) category_cnt, 
    sum(od.quantity * od.unit_price) sum_of_order_price
from orders as o
    left join order_details od on o.id = od.order_id
    left join products p on od.product_id = p.id
where '2006-01-01' <= o.order_date
    and o.order_date < '2006-04-01'
group by o.customer_id;

 

3. 2006년 3월에 주문(order)된 건의 주문 상태(status_name)를 찾는 쿼리를 작성하세요.(단, join 사용하지 않고. Hint: sub-query).

select o.id, 
       (select status_name
        from order_details_status as ods
        where o.status_id = ods.id) 
  from orders as o
 where order_date between '20060301' and '20060331'
 ;

---

/* 멘토 피드백
서브쿼리 안에 
from order_details_status as ods 에서 
ORDERS 테이블을 가져오는 것이 질문과 맞는 결정이다
*/

/* 멘토 예시답안 */
select o.id, os.id, os.status_name
from orders o
    left join orders_status os on o.status_id = os.id
where '2006-03-01' <= o.order_date 
    and o.order_date < '2006-04-01';

select id, status_id, (select status_name from orders_status os where os.id = o.status_id) status_name
from orders o
where '2006-03-01' <= order_date 
    and order_date < '2006-04-01';

4. 2006년 1분기 동안 세 번 이상 주문(order)된 상품(product)과 그 상품의 주문 수를 찾는 쿼리를 작성하세요.(order_status는 신경쓰지 않아도 된다. Hint : sub-query or having)

select od.product_id as '상품번호', count(od.order_id) as '주문 횟수'
from order_details od
left join orders o on od.order_id = o.id
where o.order_date between '20060101' and '20060331'
group by od.product_id
having count(o.id) >= 3
order by od.product_id
;

---

/* 멘토 피드백
count(od.order_id) , having count(o.id) 에서 DISTINCT를 걸어줘야 한다
*/

/* 멘토 예시 답안 */
select *
from (
    select product_id, count(distinct o.id) cnt
    from orders o
        left join order_details od on o.id = od.order_id
    where '2006-01-01' <= order_date 
        and order_date < '2006-04-01'
    group by product_id
    ) a
where cnt >= 3

select product_id, count(distinct o.id) cnt
from orders o
    left join order_details od on o.id = od.order_id
where '2006-01-01' <= order_date 
    and order_date < '2006-04-01'
group by product_id
having count(distinct o.id) >= 3;

 

5-1. 2006년 1분기, 2분기 연속으로 하나 이상의 주문(order)을 받은 직원(employee)을 찾는 쿼리를 작성하세요.( order_status는 신경쓰지 않아도 된다. Hint : sub-query, inner join)

select emp.id as '직원번호',
       emp.last_name as '성',
       emp.first_name as '이름',
       count(o.id) as '주문횟수'
from employees emp
inner join orders o on emp.id = o.employee_id
where o.employee_id in 
    (select employee_id
     from orders
     where (order_date between '20060101' and '20060331')
      and (order_date between '200600401' and '20060630')
      group by employee_id
      having count(orders.id) >= 1
    )
group by emp.id
;

---

/* 멘토 피드백
     where (order_date between '20060101' and '20060331')
      and (order_date between '200600401' and '20060630')
부분에 오타 가 있고 오타를 수정해도 ORDER_DATE가 1분기에 해당하고 2분기에 동시에 해당하는 값은 
없기 때문에 결과값이 나오지 않았을 것이다. 결과가 나오지 않을 것이다.
1분기 결과값과 2분기 결과값의 교집합(inner joint)을 걸어야 결과가 나올 것이다.
*/

/* 멘토 예시 답안 */
-- 1분기: 1,3,4,6,8,9
-- 2분기: 1,2,3,4,6,7,8,9

select o1.employee_id
from 
    (select distinct employee_id
    from orders
    where '2006-01-01' <= order_date 
        and order_date < '2006-04-01') o1
        
    inner join
    
    (select distinct employee_id
    from orders
    where '2006-04-01' <= order_date 
        and order_date < '2006-07-01') o2
        
    on o1.employee_id = o2.employee_id;

5-2. 2006년 1분기, 2분기 연속으로 하나 이상의 주문을 받은 직원별로, 2006년 월별 주문 수를 찾는 쿼리를 작성하세요.( order_status는 신경쓰지 않아도 된다. Hint : sub-query 중첩, date_format() )

select emp.id as '직원번호',
       date_format(o.order_date,'%m') as '월',
       count(o.id) as '주문횟수'
from employees emp
inner join orders o on emp.id = o.employee_id
where emp.id in 
    (select employee_id
     from orders
     where (order_date between '20060101' and '20060331')
      and (order_date between '200600401' and '20060630')
      group by employee_id
      having count(orders.id) >= 1
    )
group by emp.id,
         date_format(o.order_date,'%m' )         
;

---

/* 멘토 피드백
 where ~ 부분은 5-1번과 마찬가지로 
 오타가 있고 ORDER_DATE가 1분기에 해당하고 2분기에 동시에 해당하는 값은 
 없기 때문에 결과값이 나오지 않았을 것이다.
 1분기 결과값과 2분기 결과값의 교집합을 걸어야 결과가 나올 것이다.
문제는 2006년 월별이라고 되어있지만 
group by emp.id, date_format(o.order_date,'%m’) 에서 
%m 을 %Y%m으로 해줘야 2006년 월별로 확인이 가능하다 
*/

/* 멘토 예시 답안 */
select employee_id, date_format(order_date, '%Y-%m') ym, count(1) cnt
from orders
where employee_id in (
    select o1.employee_id
        from 
            (select distinct employee_id
            from orders
            where '2006-01-01' <= order_date 
                and order_date < '2006-04-01') o1
            inner join
            (select distinct employee_id
            from orders
            where '2006-04-01' <= order_date 
                and order_date < '2006-07-01') o2
            on o1.employee_id = o2.employee_id
        )
    and '2006-01-01' <= order_date 
    and order_date < '2007-01-01'
group by 1, 2;