유저 전환율을 sql로 구하기

https://mode.com/ 에 들어가 tutorial.yammer_events 데이터 테이블을 가지고 문제를 풀어보았다.

내가 처음 적었던 쿼리는 아래와 같았다.

WITH create_id as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'create_user'),
sign_up as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'complete_signup')

SELECT t.location,
       t.device,
       ROUND(COUNT(CASE WHEN DATE_PART('hour',  s.occurred_at::timestamp -  c.occurred_at::timestamp) < 24 THEN 1 END) * 1.0 /
       COUNT(*) * 100, 2)
FROM tutorial.yammer_events t 
LEFT JOIN create_id c on t.user_id = c.user_id
JOIN sign_up s on c.user_id = s.user_id
GROUP BY t.location, t.device

이 쿼리에는 많은 문제가 있다😂
답은 모든 행에서 100%를 반환하였는데 그 이유는 여러가지가 있겠지만 주요한 이유중에 하나가 join의 활용 이해부족이다.

나는 A,B,C 테이블을 각각 A와 B를 LEFT JOIN으로 묶고, B와 C를 INNER JOIN으로 묶으면, A의 전체 행과 B,C의 교집합이 합쳐지는줄 알았다.

하지만 내가 완전 잘못 이해하고 있었다.
당연히 있어야할 B와 C 테이블에 NULL값이 보이지 않았다..
그 이유는 아래와 같았다.

두번째 조인을 할때에 B와 C의 공통인 행을 불러오며 A테이블에서 또한 B와 C의 공통인 행들만 가져온다.

결국 마지막 순서의 JOIN이 중요하다는 생각을 하게 되었다.

정답 쿼리를 아래 작성하며 마친다.

WITH create_id as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'create_user'),
sign_up as (
SELECT *
FROM tutorial.yammer_events 
WHERE event_name = 'complete_signup')

SELECT c.location,
       c.device,
       ROUND(COUNT(DISTINCT s.user_id) * 1.0 / COUNT(DISTINCT c.user_id) * 100, 2) as conversion_percentage 
FROM create_id c
LEFT JOIN sign_up s on c.user_id = s.user_id 
and c.occurred_at <= s.occurred_at
and DATE_PART('hour', s.occurred_at ::timestamp - c.occurred_at::timestamp) <= 24
GROUP BY 1,2