내배캠 TIL 9일차

1. SQL 개인과제

"조건에 맞는 쿼리문을 작성하고 해설을 작성하기"

쿼리문을 올렸놨으니 자유로운 훈수 부탁드립니다

 

 

**문제 1:** "최소 주문 날짜 찾기"

주어진 테이블 **`list_of_orders`**에는 여러 주문들의 날짜가 저장되어 있습니다.

주문 날짜는 'dd-mm-yyyy' 형식으로 저장되어 있습니다. 
이 테이블에서 가장 이른 주문 날짜를 찾아보세요.

select a.ORDER_DATE
from (
  select STR_TO_DATE(order_date, "%Y-%m-%d") as ORDER_DATE 
  from list_of_orders
  ) a
where ORDER_DATE is not null
order by 1
limit 1

 

 

좀 살펴보니 order_date에 null값이 있어서 where로 처리

 

 

 

**문제 2:** "카테고리별 총 매출 계산하기"

**`order_details`** 테이블에는 다양한 주문들의 세부 정보가 저장되어 있습니다.

각 주문은 특정 'Category'에 속하며,
각 주문의 'Amount'는 그 주문의 매출 금액을 나타냅니다.
이 테이블을 사용하여 각 카테고리별 총 매출 금액을 계산하세요.

select Category,
   SUM(AMOUNT) as TOTAL_AMOUNT
from order_details
group by Category

 

 

 

 

 **문제 3:** "상위 5명의 고객별 총 구매액 찾기"

두 개의 테이블 **`order_details`**와 **`list_of_orders`**가 있습니다.

**`order_details`** 테이블은 각 주문의 세부사항을 포함하고 있으며,

**`list_of_orders`** 테이블은 주문에 대한 고객의 이름과 주문 ID를 포함합니다.
각 고객별로 총 구매액을 계산하여,
구매액이 가장 높은 상위 5명의 고객을 찾아보세요.

select loo.customername,
   SUM(OD.Amount) as TOTAL_AMOUNT
from list_of_orders loo 
inner join order_details od
on LOO.order_id = OD.order_id
group by LOO.customername
order by 2 desc
limit 5

 

 

unique한 값에 대한 얘기를 팀원들과 한 결과
-> 나는 customername을 기준으로 하기로 결정

 

 

**문제 4:** "여러 주문을 한 고객들의 총 구매액 계산하기"

두 테이블 **`list_of_orders`**와 **`order_details`**가 있습니다.

**`list_of_orders`** 테이블은 고객 이름과 주문 ID를,
**`order_details`** 테이블은 각 주문의 상세 금액을 포함하고 있습니다.
이 테이블들을 사용하여, 3개 이상의 주문을 한 고객들의 이름,
총 주문 횟수, 그리고 그들의 총 구매액을 계산하세요.

select distinct a.customername,
   a.total_count,
       a.total_amount
from list_of_orders loo 
inner join (select LOO.customername,
   COUNT(*) as TOTAL_COUNT,
   SUM(OD.AMOUNT) as TOTAL_AMOUNT
   from list_of_orders loo
inner join order_details od
   on LOO.order_id = OD.order_id
   group by LOO.customername) a
on loo.customername = a.customername
where TOTAL_COUNT >= 3
order by TOTAL_COUNT desc, TOTAL_AMOUNT desc

 

 

- 서브쿼리 내에서 고객 별로 count
- join 후 count를 where에서 조건으로 사용
- distinct로 동일 고객의 다른 주문들은 쳐낸다

 

 

**문제 5:** "평균 수량을 초과하는 주문 찾기"

**`order_details`** 테이블에는 다양한 주문들의 상세 정보가 저장되어 있습니다.

이 테이블의 각 행은 주문 ID(**`order_id`**), 카테고리(**`Category`**),

그리고 주문 수량(**`Quantity`**)을 포함합니다.
이 테이블을 사용하여 각 카테고리별 평균 주문 수량을 초과하는
모든 주문들을 찾아보세요.

select od.order_id,
   od.category,
       od.quantity,
       a.avg_quantity
from order_details od
inner join (select Category,
   AVG(Quantity) as avg_quantity
from order_details
group by Category) a
on od.category = a.category
where od.quantity > a.avg_quantity
order by od.quantity desc

 

앞과 마찬가지로 where에 조건으로 사용하기 위해
서브쿼리에 avg를 사용

 

 

 

> **문제 6:** "카테고리별 매출액 순위 및 누적합계 계산하기"

**`order_details`** 테이블에는 주문의 상세 정보가 포함되어 있습니다.
이 테이블은 주문 ID(**`order_id`**), 카테고리(**`Category`**), 
그리고 주문의 금액(**`Amount`**)을 포함합니다. 
이 테이블을 사용하여 각 카테고리 내에서 각 주문의 매출액 순위와 
그 카테고리 내의 누적 매출액을 계산하세요.

select order_id,
   category,
       amount,
   rank() over(partition by category order by amount desc) as amount_rank,
   sum(amount) over (partition by category order by order_id) as cum_sum_amount
from order_details
order by category, cum_sum_amount, amount

 

- rank

카테고리 내에서의 - partition by category
매출액 순위 - order by amount

- sum

카테고리 내에서의 - partition by category
누적매출액 - sum(amount)

 

 

 

**문제 7:** "주별 매출 순위 및 평균 매출 목표 달성 여부 확인"

**`list_of_orders`**, **`order_details`**, 그리고 **`sales_target`** 세 개의 테이블이 있습니다.
**`list_of_orders`** 테이블은 주문 ID(**`order_id`**)와 주문이 이루어진 주(**`State`**를,

**`order_details`** 테이블은 각 주문의 금액(**`Amount`**)과 이익(**`Profit`**)을, 
**`sales_target`** 테이블은 각 카테고리별 매출 목표(**`Target`**)를 포함합니다.

이 테이블들을 사용하여 각 주별로 주문의 총 금액과 이익을 계산하고,

각 주 내에서 주문의 매출 순위를 결정하세요.
또한, 각 주문의 총 금액이 해당 카테고리의 평균 매출 목표의 50%를 달성했는지 여부도 판단하세요.

select loo.order_id,
       loo.state,
       a.total_amount,
       od2.amount,
       rank() over(partition by loo.state order by od2.amount desc) amount_rank_in_state,
       od2.category,
       st.avg_target,
       if(od2.amount >= st.avg_target/2, 'success', 'fail') as goal
from list_of_orders loo
inner join (select loo.state,
   				   sum(od.amount) as total_amount,
      			   sum(od.profit) as total_profit
   			from list_of_orders loo
   			inner join order_details od
   			on loo.order_id = od.order_id
   			group by loo.State) a
on loo.state = a.state
inner join order_details od2
on loo.order_id = od2.order_id
inner join (select category,
				   avg(target) as avg_target
            from sales_target
            group by category) st
on od2.category = st.category
order by 2, 3 desc

- (총금액, 총이익), 매출순위, 평균매출목표

구해야할 게 많아서 그만큼 서브쿼리도 늘어남
코드가 어지럽다면 그건 저 역시도 마찬가지 상관없다고 생각합니다

 

 

2. 개인과제를 하며...

- 코드카타는 학습용으로 매우 친절한 문제였다
- 다른 사람이 보면 내 코드는 random walk
- 그만큼 나는 아직 많이 부족하다
- 아직은 코드 최적화보다는 그래도 원하는 것을 얻는 코드를 추구하자..