데이터 분석 52일
- 문제1.
- 문제 풀이
with cnt_table as (select count(*) all_cnt,
count(case when (category = 'n/a' or category is null) then 1 end) null_cnt
from calls
where call_date <= '2024-04-15 23:59:59')
select round(((null_cnt / all_cnt)*100),1) uncategorised_call_pct
from cnt_table
- 결과
- 문제2.
- 문제 풀이
select up.age_bucket,
round((sum(if(event_type = 'order', 1, 0)) / count(1)) * 100, 2) conversion_rate
from user_profiles up
join app_events ap
on up.user_id = ap.user_id
where up.signup_date >= '2023-01-01'
and event_type in ('order', 'view')
group by 1
order by 1
- 결과
- 문제 3.
- 문제 풀이
WITH n_table AS (
SELECT user_id,
MAX(CASE WHEN row_num = 1 THEN order_datetime END) AS first_date,
MAX(CASE WHEN row_num = 10 THEN order_datetime END) AS tenth_date,
COUNT(*) AS cnt
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_datetime) AS row_num
FROM user_orders
) a
GROUP BY user_id
HAVING cnt >= 10
)
SELECT user_id,
DATEDIFF(tenth_date, first_date) AS days_to_power_user
FROM n_table
ORDER BY days_to_power_user
LIMIT 1
- 결과
