QCC 3회차
문제 1. 임직원 로그인 빈도 분석
- 2023년 7월 1일부터 9월 30일까지 성공한 로그인 기준으로 직원별 로그인 횟수를 구해야 합니다.
- 성공한 로그인 기록이 없는 직원은 제외하여 로그인 횟수를 집계해주세요
- 성공한 로그인 횟수가 1회인 직원 수, 2회인 직원 수 등으로 묶어야 합니다.
- 출력 컬럼은 unique_logins (성공한 로그인수), employee_count (직원수) 입니다.
- 결과는 unique_logins 기준으로 오름차순 정렬해야 합니다.
제출한 답
-- 23년 7월 1일 ~ 9월 30일까지 성공한 로그인 횟수 컬럼을 추가한 테이블
with login_cnt as (
select
e.employee_id,
l.login_time,
l.login_result,
count(*) as unique_logins
from employees e
join logins l
on e.employee_id = l.employee_id
where (l.login_time between '2023-07-01' and '2023-09-30')
and l.login_result = 'success'
group by e.employee_id
)
-- 성공한 로그인 횟수 (unique_logins), 성공한 직원 수 (employee_count) 필터링
select unique_logins, count(*) as employee_count
from login_cnt
group by 1
order by 1 asc정답
WITH success_logins AS (
SELECT employee_id, COUNT(distinct login_id) AS unique_logins
FROM qcc.logins
WHERE login_result = 'SUCCESS'
AND login_time >= '2023-07-01'
AND login_time < '2023-10-01'
GROUP BY employee_id
)
SELECT unique_logins, COUNT(1) AS employee_count
FROM success_logins
GROUP BY unique_logins
ORDER BY unique_logins해설
일반적으로 실무에서 날짜를 필터링할 때에는 '정답'에서 작성한 것 처럼
columns >= '날짜' AND columns < '날짜+1일'로 작성한다.
이렇게 작성해야 원하는 날짜의 상한값까지 포함시킬 수 있기 때문에 더 안전하다.
문제 2. 세 번째로 높은 급여를 받는 직원
- 전체 직원 중에서 세 번째로 높은 급여 금액을 찾아야 합니다.
- 해당 금액을 받는 모든 직원을 함께 출력해야 합니다.
- 출력 컬럼은 employee_id, name, salary 입니다.
- 결과는 employee_id 기준으로 오름차순 정렬해야 합니다.
제출한 답
with salary_rnk as (
select
*,
dense_rank() over(order by salary desc) as sal_rnk
from employee_salary
)
select employee_id, name, salary
from salary_rnk
where sal_rnk = 3정답
WITH salary_ranked AS (
SELECT employee_id, name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM qcc.employee_salary
)
SELECT employee_id, name, salary
FROM salary_ranked
WHERE rnk = 3
ORDER BY employee_id해설
안타깝게도 오름차순 정렬을 하지 않아서 틀린 문제이다.
이번 QCC는 문제 수가 늘어나서 시간이 부족하다고 판단하여 문제 요구사항에 대해서 파악을 완전히 못하고 풀었다. 그래서 오름차순이 있다는 부분을 완전히 놓치고 말았다.
앞으로는 기본적인 요구 조건을 주석으로 앞에 작성해놓고 체크리스트처럼 확인해가면서 진행해야겠다.
DENSE_RANK는 기본적으로는 RANK와 동일하나, 같은 점수일 경우 같은 순위를 부여 후, 그 다음 점수는 이어서 순위를 매기기 때문에 순위의 누락없이 매길 수 있다.
문제 3. 부서 간 메세지 비율 계산
- messages 테이블을 기준으로, sender_id 와 receiver_id를 각각 employees 테이블에 조인하여 각 메시지의 송신자와 수신자의 부서를 확인해야 합니다.
- sender와 reciever의 부서가 다를 경우만 부서 간 메시지로 간주합니다.
- 전체 메시지 중 부서 간 메시지가 차지하는 비율(%)을 계산하고, 소수점 1자리까지 반올림해야 합니다.
- 출력 컬럼명은 inter_department_msg_pct 로 설정합니다.
- 보낸 사람과 받는 사람 모두 employees 테이블에서 부서 정보가 존재하는 경우에만 메시지를 유효하게 판단합니다. 즉, 부서가 없는 직원이 포함된 메시지는 분자, 분모값 등 분석 대상에서 제외합니다.
제출한 답
select
round(
sum(case
when e1.department != e2.department then 1
else 0
end) / count(*) * 100
,1) as inter_department_msg_pct
from messages m
join employees e1
on m.sender_id = e1.employee_id
join employees e2
on m.receiver_id = e2.employee_id정답
SELECT
ROUND(100.0 * SUM(CASE WHEN e1.department != e2.department THEN 1 ELSE 0 END) / COUNT(*), 1) AS inter_department_msg_pct
FROM qcc.messages m
JOIN qcc.employees e1 ON m.sender_id = e1.employee_id
JOIN qcc.employees e2 ON m.receiver_id = e2.employee_id해설
join을 두번 사용하여 보내는 사람과 받는 사람에 해당하는 부서 정보를 붙이고,
보내는 사람과 받는 사람의 부서가 다른 경우에 1을 sum으로 더하여 전체 건 수로 나누고 반올림을 진행하여 풀었다.
문제4. (도전) 광고 성과 Attribution 분석**
- 전환(converted = True)이 발생한 사용자들만 분석 대상입니다.
- 한 사용자가 여러 세션을 가질 수 있으며, 그 중 가장 처음 (created_at 기준으로)의 세션 유입 채널을 추출해야 합니다.
- 출력 컬럼은 user_id, channel 이며, 결과는 user_id 기준 오름차순 정렬해야 합니다.
제출한 답
select user_id, channel
from
(
select
a.session_id,
a.channel,
a.converted,
u.user_id,
u.created_at,
row_number() over(partition by a.session_id order by u.created_at) as session_order
from (select * from ad_attribution where converted = 1) a
join user_sessions u
on a.session_id = u.session_id
) as so
where session_order = 1정답
WITH converted_users AS (
SELECT DISTINCT us.user_id
FROM qcc.ad_attribution a
JOIN qcc.user_sessions us ON a.session_id = us.session_id
WHERE a.converted = TRUE
), first_sessions AS (
SELECT us.user_id, a.session_id, us.created_at, a.channel,
ROW_NUMBER() OVER (PARTITION BY us.user_id ORDER BY us.created_at) AS rn
FROM qcc.user_sessions us
JOIN converted_users cu ON us.user_id = cu.user_id
JOIN qcc.ad_attribution a ON us.session_id = a.session_id
)
SELECT user_id, channel
FROM first_sessions
WHERE rn = 1
ORDER BY user_id해설
이 문제는 전환된 (converted = True) 고객 중 created_at이 가장 첫번째인 user_id를 ROW_NUMBER를 사용해서 1인 것으로 필터링하고 with를 사용해서 임시 테이블 만들고 거기서 추리는 문제였다.
그렇게 어렵게 풀진 않았는데 마찬가지로 마지막에 정렬을 안해서 대차게 틀렸다.
이번 QCC는 전반적으로 with, join, 윈도우 함수를 사용하는 문제였다. 실제 체감 난이도는 크지 않았지만 문제 수가 늘어났고 정렬 조건을 맞추지 못해서 틀렸다.
QCC 및 코드테스트에서는 아래와 같은 방식으로 진행해야겠다.
- 문제 예시 결과를 확인한다.
- 문제 조건을 주석으로 정리한다.
- from을 가장 먼저 생각한다. (join을 해야하는지, 어떤 join을 써야하는지 등)
