[SQL/Classicmodels Database] classicmodel 데이터를 활용한 실습 3문제 풀이

Hyeon·2024년 11월 8일
sql

SQL 문제 풀이

목록 보기
36/61

✅출처

과제 진행 사이트 (데이터 출처)
https://www.kaggle.com/code/martatavares/classicmodels-database

ERD (database diagram)

✅과제 1

문제

-월별 H, M,L 의 주문 카운팅 수는 어떻게 되는지?
-월별의 기준은 orderDate 기준으로
-원하는 쿼리 결과물은
2003-01 H 00, M 00, L 00 ~ 2004-04 H 00, M 00, L 00

코드

-- 주문 상세 데이터 : 제품별 가격 등급 산정하기
with cte_1 as (select 
	orderNumber,
    productCode,
	case
		when priceEach >= 100 then 'H'
        when priceEach between 50 and 99 then 'M'
        else 'L'
	end as price_category
    from orderdetails),
    
-- 문제 1 
-- 고객 테이블과, 주문 테이블 이용해서 , 주문상세 테이블 이용

cte_2 as (select a.customerName,a.customerNumber,b.orderNumber, b.orderDate ,shippedDate,state,productCode, price_category
from customers a left join orders b on a.customerNumber = b.customerNumber left join cte_1 c 
on b.orderNumber = c.orderNumber)


-- 월별 H, M,L 의 주문 카운팅 수는 어떻게 되는지?

select date_format(orderDate ,'%Y-%m') as month , price_category, count(orderNumber) as count_number
from cte_2
where date_format(orderDate ,'%Y-%m') is not null and price_category is not null
group by date_format(orderDate ,'%Y-%m'), price_category
order by 1,2;

진행 과정

  1. 제품별로 높은 가격인지, 보통 가격인지, 낮은 가격인지 구분하는 컬럼을 만든다.
with cte_1 as (select 
	orderNumber,
    productCode,
	case
		when priceEach >= 100 then 'H'
        when priceEach between 50 and 99 then 'M'
        else 'L'
	end as price_category
    from orderdetails)
select * from cte_1

2.해당 컬럼을 기준으로 다른 테이블과 조인한다.

cte_2 as (select a.customerName,a.customerNumber,b.orderNumber, b.orderDate ,shippedDate,state,productCode, price_category
from customers a left join orders b on a.customerNumber = b.customerNumber left join cte_1 c 
on b.orderNumber = c.orderNumber)

3.월별 주문 카운팅 수를 출력한다.

select date_format(orderDate ,'%Y-%m') as month , price_category, count(orderNumber) as count_number
from cte_2
where date_format(orderDate ,'%Y-%m') is not null and price_category is not null
group by date_format(orderDate ,'%Y-%m'), price_category
order by 1,2;

✅과제 2

문제

-직원별 월별 매출 분석하기
-월마다 어떤 제품이 가장 많이 팔리는지 차이를 확인
-쿼리 제작시 추출 조건과 테이블 주석으로 정리한 후 작업

코드

-- step1.월마다 어떤 제품이 가장 많이 팔리는지 확인하기
with cte_1 as(
select month(orderDate) as 'month', max(d.productCode) as productCode
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber 
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped'
-- 기준 : 월별
group by month(orderDate)
-- 순서대로 출력
order by 1)
-- step2.월별 제품코드+ 제품명까지 확인하기
select month,b.productCode, productName from cte_1 a join products b on a.productCode = b.productCode;

진행 과정

  1. 테이블 조인하여 월별 어떤 제품이 가장 많이 팔리는지를 추출하기 
with cte_1 as(
select month(orderDate) as 'month', max(d.productCode) as productCode
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber 
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped'
-- 기준 : 월별
group by month(orderDate)
-- 순서대로 출력
order by 1)
select * from cte_1;

2.product 테이블을 조인함으로써, 월별 제품 코드 + 제품명까지 확인하기

select month,b.productCode, productName from cte_1 a join products b on a.productCode = b.productCode;

✅과제 3

문제

-3월 가장 많이 판매한 직원과 4월의 가장 많이 판매한 직원의 매출 차이와 제품 차이를 비교
-3월의 매출, 제품과 4월의 매출, 제품 비교
-쿼리 제작시 추출 조건과 테이블 주석으로 정리한 후 작업

코드

with cte_1 as (select month(orderDate) as 'month',employeeNumber, lastName, firstName , sum(quantityOrdered) as 'total_count'
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped' and (month(orderDate) = 3 or month(orderDate) = 4)
group by month(orderDate),employeeNumber, lastName, firstName) ,

-- step2. 월별 총 판매 개수가 높은 직원을 구하기
 cte_2 as (select month, employeeNumber, lastName, firstName
from (
select month, employeeNumber, lastName, firstName, total_count, row_number() over (partition by month order by total_count desc) as max_rank
from cte_1 ) t
where max_rank=1),

-- step3. 직원을 조인하여, 해당 월별 매출과 제품을 출력하기

-- 3-1. 월별 매출과 제품 출력 table
cte_3 as (select month(orderDate) as 'month',employeeNumber, lastName, firstName , sum(quantityOrdered * priceEach) as total_amount , max(productCode) as productCode
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped' and (month(orderDate) = 3 or month(orderDate) = 4)
group by month(orderDate),employeeNumber, lastName, firstName)

-- 3-2. 직원별 매출액과 제품 코드 테이블 & 가장 많이 판 직원 테이블 join (최종 테이블)
select a.month, a.employeeNumber, a.lastName, a.firstName, total_amount, productCode
from cte_3 a join cte_2 b 
on a.month= b.month and  a.employeeNumber = b.employeeNumber;

진행 과정

  1. 월별 및 직원별 '총 판매 개수' 테이블 구하기
-- step1. 테이블 조인 및 월별 및 직원별 총 판매 개수 컬럼 구하기
with cte_1 as (select month(orderDate) as 'month',employeeNumber, lastName, firstName , sum(quantityOrdered) as 'total_count'
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped' and (month(orderDate) = 3 or month(orderDate) = 4)
group by month(orderDate),employeeNumber, lastName, firstName) 
select * from cte_1

  1. 총 판매 개수가 높은 직원 테이블 구하기
    : 이때 window function을 활용
    : 월 기준 판매 개수가 1등인 테이블을 where 절을 활용하여 추출
-- step2. 월별 총 판매 개수가 높은 직원을 구하기
cte_2 as (select month, employeeNumber, lastName, firstName
from (
select month, employeeNumber, lastName, firstName, total_count, row_number() over (partition by month order by total_count desc) as max_rank
from cte_1 ) t
where max_rank=1)
select * from cte_2;
  1. 월별 매출과 제품을 출력하는 테이블 구하기
    : 월별 매출은 수량 * 금액 합계
-- 3-1. 월별 매출과 제품 출력 table
cte_3 as (select month(orderDate) as 'month',employeeNumber, lastName, firstName , sum(quantityOrdered * priceEach) as total_amount , max(productCode) as productCode
from 
employees a
inner join customers b on a.employeeNumber = b.salesRepEmployeeNumber
inner join orders c on b.customerNumber = c.customerNumber inner join orderdetails d on c.orderNumber = d.orderNumber
-- 이때 shipped 상태인 것만 주문하기
-- 매출로 확정되지 않은 주문이거나, 아직 배송되지 않은 주문상태는 보류
where status = 'Shipped' and (month(orderDate) = 3 or month(orderDate) = 4)
group by month(orderDate),employeeNumber, lastName, firstName)
select * from cte_3;

  1. 2번째 테이블 & 3번째 테이블을 조인하여,가장 많이 판매한 직원의 제품 코드와 매출액을 구하기
-- 3-2. 직원별 매출액과 제품 코드 테이블 & 가장 많이 판 직원 테이블 join (최종 테이블)
select a.month, a.employeeNumber, a.lastName, a.firstName, total_amount, productCode
from cte_3 a join cte_2 b 
on a.month= b.month and  a.employeeNumber = b.employeeNumber;

profile
Hyeon
이전 포스트

[SQL 심화] 리트코드 1문제 :: 177. Nth Highest Salary

다음 포스트

[SQL 레시피]

0개의 댓글