[2024.07.25 TIL] sql 문제 풀기

조건문과 subquery를 이용하여 유저 세분화(User Segmentation)하기

• 음식점의 평균 단가별 segmentation / 그룹에 따라 수수료 연산
  단가 < 5000 0.05 / 단가 < 20000 0.1 / 단가 < 30000 0.2 / 단가 >= 30000 3%

  select restaurant_name,
        price_per_plate * ratio_of_add "수수료"
  from 
  (
  select restaurant_name,
        case when price_per_plate < 5000 then 0.005
             when price_per_plate between 5000 and 19999 then 0.01
             when price_per_plate between 20000 and 29999 then 0.02
             else 0.03 end ratio_of_add,
        price_per_plate
  from 
  (
  select restaurant_name, avg(price/quantity) price_per_plate
  from food_orders
  group by 1
  ) a
  ) b

  subquery 안에 subquery 사용

• 음식점의 지역과 평균 배달 시간으로 segmentation 하기
  배달시간 <= 20 / 20 < 배달시간 <= 30 / 배달시간 > 30

  SELECT restaurant_name,
  		sido,
  		case when avg_d_time<=20 then '<=20'
  			 when avg_d_time>20 and avg_d_time<=30 then '20<x<=30'
  			 else '>30' end delivery_time_segment
  FROM
  (
  select restaurant_name,
  		SUBSTR(addr, 1, 2) sido,
  		avg(delivery_time) avg_d_time
  from food_orders
  group by 1, 2
  ) a

복잡한 연산을 subquery로 수행

• 음식 타입별 총 주문 수량과 음식점 수를 연산, 주문 수량과 음식점 수 별 수루료율 산정하기
  (음식점 수 5개 이상, 주문수 30이상 -> 수수료 0.05%,
  음식점 수 5개 이상, 주문수 30미만 -> 수수료 0.08%,
  음식점 수 5개 미만, 주문수 30이상 -> 수수료 1%,
  음식점 수 5개 미만, 주문수 30미만 -> 수수료 2%)

  SELECT cuisine_type '음식 타입',
  		total_quantity '총 주문 수량',
  		count_restaurant '음식점 수',
  		case when count_restaurant >= 5 and total_quantity >= 30 then 0.0005
  			 when count_restaurant >= 5 and total_quantity < 30 then 0.0008
  			 when count_restaurant < 5 and total_quantity >= 30 then 0.01
  			 when count_restaurant < 5 and total_quantity < 30 then 0.02
  			 end '수수료율'
  from
  (
  SELECT cuisine_type,
  		sum(quantity) total_quantity,
  		count(DISTINCT restaurant_name) count_restaurant
  FROM food_orders
  group by 1
  ) a

  

• 음식점의 총 주문 수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
  (할인 조건
  수량이 5개 이하 -> 10%
  수량이 15개 초과, 총 주문 금액이 300000 이상 -> 0.5%
  이외에는 일괄 1%)

  SELECT restaurant_name '음식점',
  		case when total_quantity <= 5 then 0.1
  			 when total_quantity > 15 and total_price >= 300000 then 0.005
  			 else 0.1 end '수수료 할인율'
  from
  (
  SELECT restaurant_name,
  	   sum(quantity) total_quantity,
  	   sum(price) total_price
  FROM food_orders
  group by 1
  ) a