문제 #1 (쉬움)
카카오 광고 분석을 위한 ad_events 테이블이 있습니다.
2022년 동안 발생한 클릭률(Click-Through Rate, CTR)을 계산하는 SQL 쿼리를 작성하세요.
결과는 ad_id 기준으로 오름차 정렬하세요. 소수점 둘째 자리까지 반올림하여 출력하세요.
📌 CTR(클릭률) 공식:
CTR = 100.0 * ( Click 수 / Impression 수 )
-- 문제 1
-- click의 합 / impression의 합
select
ad1.ad_id, round(((click_cnt / imp_cnt) * 100), 2) as ctr
from (
select
ad_id,
count(event_type) as click_cnt
from
ad_events
where event_type = 'click'
group by 1) as ad1
join (
select
ad_id,
count(event_type) as imp_cnt
from ad_events
where event_type = 'impression'
group by 1) as ad2
on ad1.ad_id = ad2.ad_id
order by 1 asc문제 #2 (중간)
쿠팡의 고객 구매 데이터를 분석하여 VIP 고객을 식별하려고 합니다.
모든 제품 카테고리에서 최소 1개 이상의 상품을 구매한 고객을 VIP 고객이라 정의합니다.
VIP 고객의 customer_id를 조회하는 SQL 쿼리를 작성하세요.
결과는 고객 ID 기준으로 오름차 정렬하세요.
-- 문제 2
-- 모든 제품 카테고리에서 1개이상씩 구매한 고객 = VIP
select customer_id
from (
select customer_id, count(distinct product_category) as pr_cnt
from (
select
product_id,
product_category
from products) as p
left JOIN (
select
product_id,
customer_id
from customer_orders c) as c
on
p.product_id = c.product_id
group by customer_id
) as a
where pr_cnt = (select count(distinct product_category) from products)
order by customer_id asc문제 #3 (중간)
네이버 블로그의 게시글 데이터를 분석하여 3일 이동 평균(rolling average)을 계산하세요.
각 사용자의 블로그 게시글 수에 대한 3일 이동 평균을 계산하여 user_id, post_date, rolling_avg_3d 값을 출력하는 SQL 쿼리를 작성하세요.
결과는 소수점 둘째 자리까지 반올림하여 출력하세요.
결과는 고객 ID, 게시글 작성 날짜 기준으로 오름차 정렬하세요.
📌 이동 평균(rolling average) 정의
- 이동 평균은 시계열 데이터를 분석할 때 일정 기간 동안의 평균을 계산하는 기법입니다.
- 이 문제에서는 각 사용자의 3일 이동평균(3-day rolling average)을 구해야 합니다.
- 즉, 각 날짜의 게시글 수와 그 이전 2일간의 게시글 수 평균을 계산해야 합니다.
- 데이터가 3일보다 적으면 가능한 범위 내에서 평균을 구합니다.
