[SQL 3-5주차]

3주차 숙제

다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

• 주중 : 25분 이상
• 주말 : 30분 이상

  select order_id,
  restaurant_name,
  day_of_the_week,
  delivery_time,
  case when day_of_the_week='Weekday' and delivery_time>=25 then 'Late'
  when day_of_the_week='Weekend' and delivery_time>=30 then 'Late'
  else 'On-time' end "지연여부"
  from food_orders

4주차 숙제

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

• 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
• 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

select restaurant_name,
case when price <=5000 then 'price_group1'
when price >5000 and price <=10000 then 'price_group2'
when price >10000 and price <=30000 then 'price_group3'
when price >30000 then 'price_group4' end price_group,
case when age <30 then 'age_group1'
when age between 30 and 39 then 'age_group2'
when age between 40 and 49 then 'age_group3'
else 'age_group4' end age_group
from
(
select a.restaurant_name,
avg(price) price,
avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1

5주차 숙제

음식 타입별, 연령별 주문건수 pivot view 만들기

select cuisine_type,
max(if(age=10, order_count, 0)) "10대",
max(if(age=20, order_count, 0)) "20대",
max(if(age=30, order_count, 0)) "30대",
max(if(age=40, order_count, 0)) "40대",
max(if(age=50, order_count, 0)) "50대"
from
(
select a.cuisine_type,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) order_count
from food_orders a inner join customers b on a.customer_id=b.customer_id
where age between 10 and 59
group by 1, 2
) t
group by 1