하루에 한개씩 코드카타를 푸는 것과 하루에 한개씩 TIL을 포스팅하는 건 완전 별개구나ㅠ 를 느꼈던 이번 주... 블로그를 아예 처음 써봐서 아직 1일 1포스팅 습관이 안 붙은 것 같은데, 오늘부터 다시 꾸준히 도전해 보자.
SQL 코드카타 76번
1. 판매 데이터를 년, 월별로 그루핑하고 (column 1,2)
2. 2021년 가입자 중 각 년월에 해당하는 구매자 수를 구한 다음 (column 3)
3. column 3을 2021년 가입한 전체 유저 수로 나눠 ratio까지 구하는 (column 4) 문제.
하루에 1개 적는 코드카타인 만큼 풀이과정을 조금 더 자세하게 적어보려고 한다. 먼저 문제에서 판매 년월별로 그루핑해야 하므로 판매 년월에 어떤 값들이 있는지 먼저 살펴봤다.
select date_format(s.sales_date,'%Y') year,
date_format(s.sales_date,'%m') month
from
online_sale s
group by 1,2
order by 1,2
이런 결과가 나왔다. 2021년에 가입한 유저는 있어도 구매는 모두 2022년에 일어났나보다.
-
그렇다면 이 테이블이 출력 결과의 기준이 되어야 하므로, user_info 테이블을 left join으로 불러오기로 했다. (key 값은 user_id)
-
어차피 문제에서 2021년 가입한 유저들 중 구매한 유저들을 년월별로 count하라는 조건이었으므로, where 절을 써서
- 2021년에 가입한 유저들로 대상을 한정해 준다.
-
그리고 년월별로 구매자 수를 count하는 컬럼을 추가해 보면
select date_format(s.sales_date,'%Y') year,
date_format(s.sales_date,'%m') month,
count(distinct s.user_id) purchased_users
from
online_sale s
left join user_info u on s.user_id = u.user_id
where u.joined like '2021%'
group by 1,2
order by 1,2;
이런 결과가 나온다. 그렇다면 2022년에 가입한 구매유저 수는 얼마일까? 위의 쿼리에서 '2021%'를 '2022%'로 바꿔준다.
※ 주의 : 하지만 이런 결과가 나왔다고 해서 구매한 유저의 전체 수가 93+11 = 104명으로 착각하면 안된다. 실제로 쿼리로 구해보면 가입일 불문하고 전체 구매유저 수는 93명인데, 여러 달에 걸쳐 복수 회 구매한 유저들이 (group by에 의해) 중복으로 count된 것이다. 2022년 가입 유저의 예만 봐도, 1명이 서로 다른 달에 2번 구매한 내역이 있어 실제 구매유저 수는 11명이 아닌 10명이다.
아무튼, 문제에서 요구하는 3가지 조건 중
1. 판매 데이터를 년, 월별로 그루핑하고 (column 1,2)
2. 2021년 가입자 중 각 년월에 해당하는 구매자 수를 구한 다음 (column 3)
3. column 3을 2021년 가입한 전체 유저 수로 나눠 ratio까지 구하는 (column 4) 문제.
3번 조건만이 남았다. 2021년에 가입한 유저의 전체 수를 구하는 쿼리는
select count(distinct user_id) from user_info
where joined like '2021%'이므로, 위에서 구한 column3와 합쳐서 column4를 정의하면
round(count(distinct s.user_id)/(select count(distinct user_id) from user_info
where joined like '2021%'),1) purchased_ratio이렇게 된다. 문제에서 지시한 대로 반올림한 결과를 1자리수까지만 표현해주기 위해 round 함수를 썼고, 컬럼명을 puchased_ratio로 지정해 준다. 아울러 문제에서 요구한 대로 정렬 조건까지 표현해 주면
select date_format(s.sales_date,'%Y') year,
date_format(s.sales_date,'%m') month,
count(distinct s.user_id) purchased_users,
round(count(distinct s.user_id)/(select count(distinct user_id) from user_info
where joined like '2021%'),1) purchased_ratio
from
online_sale s
left join user_info u on s.user_id = u.user_id
where u.joined like '2021%'
group by 1,2
order by 1,2;정답 쿼리가 된다.
2021년도 전체 가입자 수가 필요해서 column 4를 정의하는 데만 서브쿼리를 사용했고, 그 밖의 나머지 값들은 left join으로도 구할 수 있었다. 만약 문제가 좀 더 복잡해져서
- 년월별로 가입자 수를 구하고
- 그 년월에 가입한 사람 중에 구매이력이 있는 자와 없는 자를 구분한 다음
- ratio를 구해봐라
라는 식으로 출제됐으면 서브쿼리를 써야 했을 것 같기도...나와 다르게 푼 사람들의 답도 2~3개 정도 보고 정리해야지.
