**[문제]**마케팅 팀으로 전환된 사용자 중 최초 방문시 어떤 광고 채널로 유입되었는지 확인하기

💫ad_attribution 💫 user_sessions

image.png

image.png

image.png

<aside> 💡

sql쿼리 작성 순서

  1. 전환 된 사람만 뽑아내기
  2. 가장 처음 확인한 채널 확인하기

[풀이]

1. 전환 된 사람만 뽑아내기

where user_id in (select user_id
                  from user_sessions u join ad_attribution 
                       n u.session_id = a.session_id
                  where converted =1)
  1. conveted가 1인 사람만 뽑아낸 후
  2. in을 사용하여 user_id가 전환된 (conveted가 1인 사람) 사람만 선택합니다.

2. 가장 처음 확인한 채널 확인하기

select u.session_id, user_id,created_at ,channel
      ,rank () over(partition by user_id order by created_at desc) as r
from user_sessions u join ad_attribution a
     on u.session_id = a.session_id

image.png

  1. created_at 기준으로 rank를 설정합니다. 이때 user_id 기준으로 새로 rank설정

[정답]

with ran as (select u.session_id, user_id,created_at ,channel
                    ,rank () over(partition by user_id order by created_at desc) as r
              from user_sessions u join ad_attribution a
                   on u.session_id = a.session_id
              ****where ****user_id in (**select** user_id
                                from user_sessions u join ad_attribution 
                                     on u.session_id = a.session_id
                                where converted =1)
              group by user_id )
select session_id, channel
from ran
where r =1
order by user_id

그 후 user_id 별로 channel을 나타내 주면 끝이 납니다.