[SQL] subquery 강의 4주차

4주차 3

3. User Segmentation 와 조건별 수수료
   3-1. 평균 단가별 segment와 수수료 계산

select restaurant_name, unit_price * ratio fee 
from
	(
	SELECT restaurant_name, unit_price, 
		CASE 
			when unit_price < 5000 then 0.005
			when 5000 <= unit_price < 20000 then 0.01
			when 20000 <= unit_price < 30000 then 0.02
			else 0.03
		END as ratio
	from
		(
		SELECT restaurant_name, round(avg(price / quantity)) unit_price
		FROM food_orders 
		group by restaurant_name 
		) AUP  # average of unit price
	) ROF  # ratio of fee
;

전체적으로 수월하게 진행되었고,
그나마 헤맨 부분은 처음에 음식점별 평균 단가를 구할 때 주문마다 price와 quantity가 있는 걸 단가로 구하고,
이걸 음식점으로 group by해서 avg로 묶는 부분까지 구상하는 게 즉각적으로 떠오르지 않았다
그래도 천천히 생각하면서 잘 진행되어서 개인적으로는 만족

그리고 수수료를 구하라고 했는데 case문으로 수수료 비율까지만 구하고 만족해하면서 끝냈었다.
문제는 역시 꼼꼼히 읽어야 했는데.

3-2. 지역과 평균 배달 시간으로 segment

select restaurant_name name,
	   sido,
       case
       	when avg_delivery_time <= 20 then '<=20'
        when avg_delivery_time > 20 and avg_delivery_time <= 30 then '<=30'
        else '30<'
       end
from 
(
select restaurant_name,
	   substr(addr, 1, 2) sido,
       avg(delivery_time) avg_delivery_time       
from food_orders
group by 1, 2 # 1번째, 2번째 칼럼으로 연산 진행
# group by restaurant_name
) result
;

4. 복잡한 연산 수행하기
   4-1. 음식 타입별 총 음식점 수와 주문 수
   내 답변

select cuisine_type,
	   rc, 
       oc,
      case
          when rc >= 5 and oc >= 30 then '0.005'
          when rc >= 5 and oc < 30 then '0.008'
          when rc < 5 and oc >= 30 then '0.01'
          when rc < 5 and oc >< 30 then '0.02'
      end 
from
(
select     cuisine_type,
	restaurant_name,
	count(*) rc, # restaurant_count
	sum(quantity) oc # order_count
from food_orders
group by cuisine_type, restaurant_name
) result
;

강의 답변

select cuisine_type, 
				total_quantity,
				count_res,
       case when count_res>=5 and total_quantity>=30 then 0.005
            when count_res>=5 and total_quantity<30 then 0.008
            when count_res<5 and total_quantity>=30 then 0.01
            when count_res<5 and total_quantity<30 then 0.02 end rate
from
(
select cuisine_type,
       sum(quantity) total_quantity,
       count(distinct restaurant_name) count_res
from food_orders
group by 1
) a
;

놓쳤던 포인트
1) count(distinct restaurant_name)
cuisine_type으로 group by를 하고나서 다시 음식점 수를 구하려다보니 중복된 restaurant_name이 있기에 group by를 이중으로 실행했다
하지만 count() 함수에 distinct를 통해 중복값을 제거하면 복잡하게 코드를 작성하지 않고도 손쉽게 음식점의 수를 구할 수 있다는 점을 놓치고 있었다

2) Type
값에 ''를 사용하면 type이 int나 float가 아니라 string이 된다는 것을 간과했다.
이걸 추후 수수료 비용 산정에 활용해야 한다고 생각하면 case문을 통해 수수로율을 int값으로 받는 것이 타당하다

4-2. 주문 수량, 주문 금액 기반 수수료율 산정
내 답변

select restaurant_name,
	total_order,
	case
	   when total_order <= 5 then 0.1
	   when total_order > 15 and total_price >= 300000 then 0.005
	   else 0.01
	end ratio
from
(
select restaurant_name,
	sum(quantity) total_order,
	sum(price) total_price
from food_orders
group by restaurant_name
) result
;

강의 답변

select restaurant_name,
       case when sum_of_quantity<=5 then 0.1
            when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
            else 0.01 end ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_of_quantity,
       sum(price) sum_of_price
from food_orders
group by 1
) a

6. join으로 두 테이블 조회하기
   6-1. 한국 음식의 주문별 결제 수단과 수수로율

select
	f.order_id,
	f.restaurant_name,
	f.price,
	p.pay_type  # 결제 수단
	p.vat  # 수수로율
from food_orders f left join payments p
	on f.order_id = p.order_id
where f.cuision_type = 'Korean'
;

6-2. 고객의 주문 식당 조회

select
	distinct c.name,
	c.age,
	c.gender,
	f.restaurant_name
from customers c inner join food_orders f on c.customer_id= f.customer_id
order by c.name
;

중복 제거는 distinct.
칼럼 앞에 적으면 적용. 잊지 말자

7-2.

select cuisine_type,
	sum(price) total_price,
	sum(price * discount_rate) discounted_price
from
(
select f.cuisine_type,
	f.price,
	(age - 50) * 0.005 discount_rate
from food_orders f left join customers c
	on f.customer_id = c.customer_id
where age >= 50
) result
group by cuisine_type
order by discounted_price desc # or 3
;

숙제.

select restaurant_name,
	CASE 
		when avg_price between 5001 and 10000 then 'price_group3'
		when avg_price between 10001 and 30000 then 'price_group2'
		when avg_price > 30000 then 'price_group1'
		else 'price_group4'
	END price_group,
	CASE 
		when avg_age between 0 and 29 then 'age_group1'
		when avg_age between 30 and 39 then 'age_group2'
		when avg_age between 40 and 49 then 'age_group3'
		else 'age_group4'
	END	
from
(
SELECT aa.restaurant_name, 
	avg(unit_price) avg_price,
	avg(age) avg_age
FROM 
	(
	SELECT f.restaurant_name ,
		f.price / f.quantity unit_price,
		c.age 
	FROM food_orders f inner join customers c
		on f.customer_id = c.customer_id 
	) aa
group by restaurant_name
) bb
order by bb.restaurant_name
;