SQL04

subquery 문 기본 구조

select column1, special_column

from

( /* subquery */

select column1, column2 special_column

from table1

) a

 

음식 준비 시간이 25분 초과 하는 경우 찾아내기

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time

from

(

select order_id, restaurant_name, food_preparation_time-25 over_time

from food_orders

) a

 

음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 -    5000원 미만 0.05%  /  20000원 미만 1% / 30000원 미만 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

 

음식점의 지역과 평균 배달시간으로 segmentation 하기

SELECT restaurant_name,

               sido,

               avg_delivery_time,

           case when avg_delivery_time <= 20 then '<=20'

                   when avg_delivery_time > 20 and avg_delivery_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_delivery_time

FROM food_orders

group by 1, 2

) a

 

음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%

음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%

음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%

음식점수 5개 미만, 주문수 30개 미만 → 수수료 2%)

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

)

 

음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

(할인조건

수량이 5개 이하 → 10%

수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%

이 외에는 일괄 1%)

SELECT restaurant_name,

               total_quantity,

               earning,

               case when total_quantity <= 5 then 0.1

                       when total_quantity > 15 and earning >= 300000 then 0.005

                       else 0.01 end discount_rate

FROM

(

SELECT restaurant_name,

               sum(quantity) total_quantity,

               sum(price) earning

from food_orders

group by 1

) a