아무리 사소하더라도 배움이 없는 날은 없다.
SQL 코드카타 175번
간단한 서브쿼리 + 조인 문제.
먼저 하나의 challenge에 같은 hacker_id가 여러 번 도전할 수 있으므로, 각 hacker_id별, challenge별로 가장 높은 점수를 먼저 구한다.
select hacker_id, challenge_id, max(score) as max
from Submissions
group by 1,2그리고 나서 hacker_id별로 점수를 전체 합산해야 하므로
위의 결과를 서브쿼리로 처리하고 전체 sum을 해 준다.
select a.hacker_id, sum(max)
from
(select hacker_id, challenge_id, max(score) as max
from Submissions
group by 1,2) a
group by 1hacker 테이블에서 name을 가져와야 하므로 hacker_id를 기준으로 left join하고
select a.hacker_id, h.name, sum(max)
from
(select hacker_id, challenge_id, max(score) as max
from Submissions
group by 1,2) a
left join Hackers h on a.hacker_id = h.hacker_id
group by 1,2합산 결과가 0인 결과는 제외해 주고
점수 합계를 기준으로 내림차순 정렬, 동률일 경우는 hacker_id 기준으로 오름차순 정렬한다.
SELECT a.hacker_id,
h.name,
Sum(max)
FROM (SELECT hacker_id,
challenge_id,
Max(score) AS max
FROM submissions
GROUP BY 1,2) a
LEFT JOIN hackers h
ON a.hacker_id = h.hacker_id
GROUP BY 1,2
HAVING Sum(max) <> 0
ORDER BY 3 DESC, 1 SQL 챌린지 세션 5회차 과제 1번
page likes가 없는 page의 id를 구하는 문제.
page_id를 기준으로 left join한 후, (like가 없으면 like한 유저 id가 null로 뜰 것이므로) where절에 기준을 적어주면 된다.
SELECT p.page_id
FROM pages p
LEFT JOIN page_likes pl
ON p.page_id = pl.page_id
WHERE pl.user_id IS NULL
ORDER BY 1 SQL 챌린지 세션 5회차 과제 2번
이건 join을 할 필요도 없이 그냥 finish_date가 없는 경우만 찾으면 됨.
SELECT part,
assembly_step
FROM parts_assembly
WHERE finish_date IS NULLSQL 챌린지 세션 5회차 과제 3번
call_category가 null이거나 n/a로 표시된 건수의 비중을 구하는 문제.
분자에 해당하는 조건을 본 쿼리에 적어주고 분모는 서브쿼리로 넣으면 된다.
policy_holder_id는 중복되지만 case_id는 고유한 값이므로,
case_id를 기준으로 count하면 distinct를 넣네 마네 생각할 필요 없다.
SELECT Round(100.0 * Count(case_id) / (SELECT Count(case_id)
FROM callers), 1) AS
uncategorised_call_pct
FROM callers
WHERE call_category IS NULL
OR call_category = 'n/a' SQL 챌린지 세션 3회차 과제 리뷰.
MySQL 환경에서 퍼널 정의 + 퍼널별 전환율 계산하기.
다른 문제들은 모두 맞았는데, 3번 문제만 튜터님의 풀이와 내가 푼 결과가 달랐다.
더 이상한 건 앞의 두 구간 전환율은 맞았는데 뒤의 두 구간 전환율만 차이가 난다는 것.
먼저 아래는 내가 작성한 쿼리와 그 결과.
WITH creation AS (SELECT user_id, occurred_at AS create_time FROM s1.YAMMER_EVENTS WHERE event_name = 'create_user'),
signup AS (SELECT user_id, occurred_at AS signup_time FROM s1.YAMMER_EVENTS WHERE event_name = 'complete_signup'),
homepage AS (SELECT user_id, occurred_at AS home_time FROM s1.YAMMER_EVENTS WHERE event_name = 'home_page'),
likemessage AS (SELECT user_id, occurred_at AS like_time FROM s1.YAMMER_EVENTS WHERE event_name = 'like_message')
SELECT
round(100*count(DISTINCT signup.user_id)/(SELECT count(DISTINCT user_id) FROM creation),2) AS signup_creation_cvr,
round(100*count(DISTINCT homepage.user_id)/(SELECT count(DISTINCT user_id) FROM signup),2) AS homepage_signup_cvr,
round(100*count(DISTINCT likemessage.user_id)/(SELECT count(DISTINCT user_id) FROM homepage),2) AS likemessage_homepage_cvr,
round(100*count(DISTINCT likemessage.user_id)/(SELECT count(DISTINCT user_id) FROM signup),2) AS likemessage_signup_cvr
FROM creation
LEFT JOIN signup ON creation.user_id = signup.user_id AND signup.signup_time <= date_add(create_time, INTERVAL 30 minute)
LEFT JOIN homepage ON signup.user_id = homepage.user_id AND homepage.home_time <= date_add(create_time, INTERVAL 30 minute)
LEFT JOIN likemessage ON homepage.user_id = likemessage.user_id AND likemessage.like_time <= date_add(create_time, INTERVAL 30 minute);
그리고 다음은 튜터님의 정답 코드와 그 결과다.
WITH creation_user AS (
SELECT user_id
, occurred_at AS creation_time
, device
, location
FROM events
WHERE event_name = 'create_user'
), completion_signup AS (
SELECT user_id
, occurred_at AS signup_time
FROM events
WHERE event_name = 'complete_signup'
), homepage_enter AS (
SELECT user_id
, occurred_at AS homepage_time
FROM events
WHERE event_name = 'home_page'
), like_msg AS (
SELECT user_id
, occurred_at AS like_message_time
FROM events
WHERE event_name = 'like_message'
), full_funnel AS (
SELECT cu.user_id
,cs.user_id AS cs_id
,he.user_id AS he_id
,lm.user_id AS lm_id
,creation_time
,signup_time
,homepage_time
,like_message_time
FROM creation_user cu
LEFT JOIN completion_signup cs ON cu.user_id = cs.user_id
AND TIMESTAMPDIFF(MINUTE, cu.creation_time, cs.signup_time) <= 30
AND cu.creation_time <= cs.signup_time
LEFT JOIN homepage_enter he ON cu.user_id = he.user_id
AND TIMESTAMPDIFF(MINUTE, cu.creation_time, he.homepage_time) <= 30
AND cs.signup_time <= he.homepage_time
LEFT JOIN like_msg lm ON cu.user_id = lm.user_id
AND TIMESTAMPDIFF(MINUTE, cu.creation_time, lm.like_message_time ) <= 30
AND he.homepage_time <= lm.like_message_time
)
SELECT
-- 계정 생성 후 회원가입 완료 전환율.
ROUND((COUNT(DISTINCT cs_id)* 100.0 / COUNT(DISTINCT user_id)) , 2) AS creation_signup_rate
-- 회원가입 완료 후 홈페이지 진입률.
,ROUND((COUNT(DISTINCT he_id )* 100.0 / COUNT(DISTINCT cs_id)), 2) AS signup_homepage_entry_rate
-- 홈페이지 진입 후 메시지 좋아요 클릭률.
,ROUND((COUNT(DISTINCT lm_id )* 100.0 / COUNT(DISTINCT he_id )), 2) AS homepage_entry_like_message_rate
-- 회원가입 후 메시지 좋아요 클릭 전환율.
,ROUND((COUNT(DISTINCT lm_id )* 100.0 / COUNT(DISTINCT cs_id)), 2) as signup_like_message_rate
FROM full_funnel
내 풀이와 다른 점은 아래의 네 가지였다.
-
나는 각각의 단계들에 해당하는 CTE만 WITH절로 만들어 두고 본 쿼리를 구했는데, 튜터님은 아예 full funnel이라는 이름의 테이블까지 다 만들어 두고 본 쿼리에서는 전환율만 구했다. 오류 가능성을 줄이는 관점에서든, 다른 연산으로의 확장성 면에서든 튜터님의 방식이 더 나을 것 같다.
-
join의 조건에서 나는 '모든 단계는 처음 유저가 계정을 생성한 후 30분 내에 이루어져야 한다'는 문제의 지문 때문에 그 조건만 지정해 주었는데, 튜터님은 퍼널상 뒷 단계의 시간이 앞 단계보다 크거나 같아야 한다는 조건도 명시했다.
가볍게 생각하면 당연히 퍼널의 뒷 단계 시간이 앞 단계보다 클 테니 굳이 명시해줘야 하나 싶은데, 실제로 데이터가 쌓일 때 기술적인 오류로 시간순서가 꼬이거나 동일한 이름의 이벤트가 여러 시간대에 찍힌다거나 할 수 있으니 조건에 분명히 명시해 줘야 한다는 게 튜터님의 설명.
채찍피티에 물어봐도 이 조건이 명시되었기 때문에 이 쿼리가 이벤트의 순차 발생을 보장할 수 있게 된다고 한다. -
나는 left join을 실행할 때 조건으로 무조건 직전 단계의 user id를 key값으로 join했는데, 튜터님은 첫 번째의 create_user 테이블에 있는 user_id를 모든 join의 key값으로 사용했다는 점이 달랐다. 이것도 생각해 보면 첫 퍼널을 통과한 사람들만을 대상으로 계속 join해 나가기 때문에 퍼널 간 이벤트의 순차 발생을 보장해주는 하나의 장치가 될 수 있다. join의 조건을 나처럼 쓰면 앞 단계 이벤트는 찍히지 않았는데 중간 단계부터 이벤트가 찍힌 유저도 조건만 맞으면 퍼널에 혼입될 수 있기 때문.
-
각 퍼널의 단계가 최초 계정 생성으로부터 30분 내에 이루어져야 한다는 조건을 명시할 때, 나는 signup.signup_time <= date_add(create_time, INTERVAL 30 minute) 이렇게 date_add와 interval 30 minute으로 표시했고 튜터님은 timestampdiff(minute, create_time, signuptime) <= 30 이렇게 표시했다. 이건 쿼리의 의미에 영향을 주는 요소는 아니라 그냥 표현법의 차이로만 생각해도 될 것 같다.
정리하면,
- 퍼널 첫 단계를 통과한 사람들만 다음 단계로 이행되어야 하므로, left join할 때 key값을 첫 퍼널 통과자 id로 통일
- 퍼널의 뒷 단계가 앞 단계보다 시간상 뒤에 있다는 점을 조건에 명시
- +α) full funnel까지 미리 CTE로 지정해 두면 편하게 값을 골라 쓸 수 있고, 문제가 생겼을 때 디버깅도 간편함. 이건 쿼리를 짜는 센스 측면에서 알아두면 좋을 듯.
이 정도로 요약할 수 있겠다.
