QCC 5회차

날짜 조건이 있으면 먼저 필터링하는게 좋음

문제 1

with dateformat as (
            select count(*) as sub_cnt,id
            from calls
            where category = 'n/a' or category is null 	            
)

select round((count(*)/sub_cnt)*100,1)  as uncategorised_call_pct
from calls a
join dateformat b
on a.id = b.id
where date_format(call_date,'%Y-%m-%d')  < '2024-04-15'

문제 2

with order_cnt as 
  (
     select event_id,count(*) as sub_cnt
     from app_events 
     where event_type ='view' or  event_type = 'order'
  
)
select u.age_bucket,
       round((count(*) / sub_cnt)*100,2) as conversion_rate
       
from order_cnt o 
join user_profiles u
on o.event_id = u.user_id

문제 3

with great_cus as (
  select user_id,order_datetime as great_date,count(*) as cnt
  from user_orders
  group by 1
  having cnt >= 10
  )

select u.user_id,
      DATEDIFF (great_date,min(u.order_datetime)) as days_to_power_user
from user_orders u
join great_cus g
on u.user_id = g.user_id
group by 1