카카오 쿼리테스트 5문제

1. 23년 7월에 신규 회원가입을 하고 회원 가입일로부터 7일 이내 구매를 한 유저들이 몇 명인지, 그리고 이 고객들 중 23년 11월에 재구매한 고객들의 수는 몇 명인지를 추출하는 쿼리를 작성해 주세요.
   1. 내가 푼 문제

select count(distinct customer_id) '7일 이내 구매 고객수', count(distinct case when o.order_date like '2023-11%' then customer_id end) '11월 재구매 고객수'
from orders o 
left join customers c 
using (customer_id)
where c.join_date like '2023-07%'
and datediff(c.join_date,o.order_date) <= 7

      2. 정답

select count(distinct o.customer_id) '7일 이내 구매 고객수', count(distinct case when o.order_date like '2023-11%' then o.customer_id end) '11월 재구매 고객수'
from orders o 
left join customers c 
using (customer_id)
join orders o2 on o2.customer_id = o.customer_id 
where c.join_date like '2023-07%'
and datediff(c.join_date,o2.order_date) <= 7

-> 하나의 customer_id 에 order_date 열 두개 붙이기 위해서 orders 테이블 join을 두번 써줌. → order_date 두개 모두 찾기 가능.

 

 

2. 22년 한 해 동안 100만원 이상 구매한 우량 고객들이, 첫 구매 이후 누적 구매 주문 금액 50만원에 도달할 때까지 걸린 기간(일)을 추출하는 쿼리를 작성해 주세요.

 

# 2. 22년 한 해 동안 100만원 이상 구매한 우량 고객들이, 첫 구매 이후 누적 구매 주문 금액 50만원에 도달할 때까지 걸린 기간(일)을 추출하는 쿼리를 작성해 주세요.

select order_date 
from orders o 
where customer_id in (select customer_id 
from orders o 
group by 1
having sum(total_amount) > 1000000)
group by 1
having sum(total_amount) > 500000

select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o 

# 100만원 구매한 두명의 id를 알아냄
select distinct customer_id 
from (select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o ) a
where order_date like '2022%' and sum_amount > 1000000

# 얘네들이 어떤걸 구매했나 확인
select *
from orders o 
where customer_id in (select distinct customer_id 
from (select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o ) a
where order_date like '2022%' and sum_amount > 1000000)

# 해당 애들한테 0,1 부여 전단계에 단순히 sum over 와 case when만 추가함.
select *,
sum(total_amount) over (partition by customer_id order by order_date asc), 
case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 else 0 end as '50초과여부'
from orders o 
where customer_id in (select distinct customer_id 
from (select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o ) a
where order_date like '2022%' and sum_amount > 1000000)

# 100~~50~~도달 날짜 -> 이걸로 하면 안되고 rank over 추가해야함
select min(c.order_date)
from (select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 end as 'dodal'
from orders o 
where customer_id in (select distinct customer_id 
from (select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o ) a
where order_date like '2022%' and sum_amount > 1000000)) c
where dodal = 1
group by c.customer_id

select min(c.order_date)
from (select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 end as 'dodal'
from orders o 
where customer_id in (select distinct customer_id 
from (select * , sum(total_amount) over (partition by customer_id order by order_date asc) as sum_amount
from orders o ) a
where order_date like '2022%' and sum_amount > 1000000)) c
where dodal = 1
group by c.customer_id

# 50 넘는 순간 포착하기 위해 누적50 되면 1 안되면 0
select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 else 0 end as 'dodal'
from orders o2 
where customer_id in (select customer_id 
from orders o 
where order_date like '2022%'
group by customer_id 
having sum(total_amount) > 1000000)

# 위에꺼에 랭킹을 매김 customer_id와 dodal로 partition by
SELECT *, RANK() OVER (partition by customer_id, dodal ORDER BY order_date) AS ranking
    FROM (select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 else 0 end as 'dodal'
from orders o2 
where customer_id in (select customer_id 
from orders o 
where order_date like '2022%'
group by customer_id 
having sum(total_amount) > 1000000)) c

#ranking = 1만 추출
SELECT *
FROM (
    SELECT *, RANK() OVER (partition by customer_id, dodal ORDER BY order_date) AS ranking
    FROM (select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 else 0 end as 'dodal'
from orders o2 
where customer_id in (select customer_id 
from orders o 
where order_date like '2022%'
group by customer_id 
having sum(total_amount) > 1000000)) c
) AS ranked_dates
WHERE ranking = 1

# customer_id 별 max-min date
select customer_id , datediff(max(order_date),min(order_date)) '누적50도달날짜'
from (SELECT *
FROM (
    SELECT *, RANK() OVER (partition by customer_id, dodal ORDER BY order_date) AS ranking
    FROM (select *, case when sum(total_amount) over (partition by customer_id order by order_date asc) > 500000 then 1 else 0 end as 'dodal'
from orders o2 
where customer_id in (select customer_id 
from orders o 
where order_date like '2022%'
group by customer_id 
having sum(total_amount) > 1000000)) c
) AS ranked_dates
WHERE ranking = 1) d
group by customer_id

 

 

 

 

3. 23년 2월에 ‘식품' 카테고리와 ‘생활' 카테고리를 모두 구매한 고객들이, 23년 11월에는 어떤 카테고리를 구매했는지를 추출하는 쿼리를 작성해 주세요

 

select customer_id ,category
from orders o2 
where customer_id in (select distinct customer_id 
						from orders o 
						where order_date like '2023-02%' and category IN ('Food','Living'))
and order_date like '2023-11%'

 

 

 

4.23년 7월에 발송한 ‘Onboarding’ 캠페인의 발송 수, 클릭 수, 클릭률, 구매 전환수, 구매 전환율을 추출하는 쿼리를 작성해 주세요.

1. 클릭에 대한 기여는 메시지를 받은 후 (수신) ‘24시간' 이내에 클릭한 건만 인정
2. 구매 전환에 대한 기여는 메시지를 클릭한 후 ‘24시간’ 이내에 구매한 건만 인정

select *
from crm_push cp 
left join orders o
using (customer_id)
where cp.campaign_id = 'Onboarding'

select count(msg_id), 
count(click_date), 
concat(round(count(case when datediff(cp.receive_date ,cp.click_date) <= 1 then click_date end)*100/count(msg_id)),'%'), 
count(case when datediff(o.order_date,cp.click_date) <= 1 then order_date end),
concat(round(count(case when datediff(o.order_date,cp.click_date) <= 1 then order_date end)*100/count(click_date)),'%')
from crm_push cp 
left join orders o
using (customer_id)
where cp.campaign_id = 'Onboarding'

 

 

 

5. 23년 11월 1일을 기준으로 마지막 구매 이후 6개월(180일)이 지난 고객들의 수가 몇 명인지, 또 이 고객들이 마지막에 구매했던 카테고리가 무엇인지를 추출하는 쿼리를 작성해 주세요.

 

 

use kakao_database

select count(*)
from(select *
from orders a
where order_date in (select max(order_date)
					from orders
					group by customer_id 
					)
having datediff('2023-11-01', order_date) >= 180) c



select customer_id , category 
from(select *
from orders a
where order_date in (select max(order_date)
					from orders
					group by customer_id 
					)
having datediff('2023-11-01', order_date) >= 180) c