💡참고한 사이트 (출처 사이트)
Name: MySQL Sample Database classicmodels
Link: http://www.mysqltutorial.org/mysql-sample-database.aspx

직원들, 상품, 주문 데이터가 종합적으로 들어있는 데이터로 해당 데이터를 활용하여 월별 직원들의 매출 비교 테이블과 제품 비교 테이블을 구상하였음

문항 1

1.직원별 월별 매출 비교 시트

select salesRepEmployeeNumber as employee_id ,DATE_FORMAT(orderDate, '%m') AS order_month , sum(quantityOrdered) as total_quantity, sum(quantityOrdered * priceEach) as total_sales
from (select f.orderNumber,salesRepEmployeeNumber,orderDate,quantityOrdered,priceEach
from (select d.customerNumber, salesRepEmployeeNumber,orderNumber,orderDate,status from (select  b.salesRepEmployeeNumber,customerNumber from employees a right outer join customers b on a.employeeNumber = b.salesRepEmployeeNumber where b.salesRepEmployeeNumber is not null) c right join orders d on c.customerNumber = d.customerNumber) e
right join orderdetails f 
on e.orderNumber = f.orderNumber) t
group by salesRepEmployeeNumber,month(orderDate);

2.상품별 월별 매출 비교 시트

• 월마다 어떤 제품이 가장 잘 나가며 , 매출이 어떻게 되는지 확인하기

select salesRepEmployeeNumber as employee_id ,DATE_FORMAT(orderDate, '%m') AS order_month , sum(quantityOrdered) as total_quantity, sum(quantityOrdered * priceEach) as total_sales
from (select f.orderNumber,salesRepEmployeeNumber,orderDate,quantityOrdered,priceEach
from (select d.customerNumber, salesRepEmployeeNumber,orderNumber,orderDate,status from (select  b.salesRepEmployeeNumber,customerNumber from employees a right outer join customers b on a.employeeNumber = b.salesRepEmployeeNumber where b.salesRepEmployeeNumber is not null) c right join orders d on c.customerNumber = d.customerNumber) e
right join orderdetails f 
on e.orderNumber = f.orderNumber) t
group by salesRepEmployeeNumber,DATE_FORMAT(orderDate, '%m');

문항 2

• 3월 가장 많이 판매한 직원과 4월의 가장 많이 판매한 직원의 매출 차이와 제품 차이를 비교

SELECT order_month,productCode,productName,total_quantity,total_sales
FROM (select DATE_FORMAT(o.orderDate, '%m') AS order_month,p.productCode, p.productName,SUM(od.quantityOrdered) AS total_quantity,SUM(od.quantityOrdered * od.priceEach) AS total_sales,
      ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(o.orderDate, '%m') ORDER BY SUM(od.quantityOrdered * od.priceEach) DESC) AS rn
    FROM orders o JOIN orderdetails od ON o.orderNumber = od.orderNumber JOIN products p ON od.productCode = p.productCode WHERE  o.status = 'Shipped' GROUP BY order_month,p.productCode,p.productName) sub
WHERE rn = 1
ORDER BY order_month ASC;

매출 기준 3월의 직원 vs 4월의 직원 비교

(select * 
from cte_1
where order_month in ('03') 
order by total_quantity desc limit 1 )
UNION ALL
(select * 
from cte_1
where order_month in ('04') 
order by total_quantity desc limit 1 );

-> 3월에 가장 많이 판 직원의 판매 개수는 1726개, 4월은 1257개로 3월이 4월보다 앞서며, 매출액 역시 차이가 크다.

매출 기준 3월의 제품 vs 4월의 제품 비교

(select * 
from cte_2
where order_month in ('03') 
order by total_quantity desc limit 1 )
UNION ALL
(select * 
from cte_2
where order_month in ('04') 
order by total_quantity desc limit 1 );

-> 그러나 제품 자체로 보았을 때는 제품 3월보다 4월이 앞서며, 제품 수량은 2배 차이가 난다.모두 s10 카테고리이다.