상품을 구매한 회원 비율 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/131534#qna

프로그래머스

 

 

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

---

예시

 

예를 들어 USER_INFO 테이블이 다음과 같고

USER_ID	GENDER	AGE	JOINED
1	1	26	2021-06-01
2	NULL	NULL	2021-06-25
3	0	NULL	2021-06-30
4	0	31	2021-07-03
5	1	25	2022-01-09
6	1	33	2022-02-14

ONLINE_SALE 이 다음과 같다면

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
4	6	253	3	2022-02-03
5	2	31	2	2022-02-09
6	5	35	1	2022-02-14
7	5	57	1	2022-02-18

2021년에 가입한 회원은 USER_ID가 1, 2, 3, 4 인 회원들로 총 4명 입니다. ONLINE_SALE 테이블에서 해당 회원들에 대한 판매 데이터는 다음과 같습니다.

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
5	2	31	2	2022-02-09

그러므로 년, 월 별로 상품을 구매한 회원수와 상품을 구매한 회원의 비율을 구하고 결과를 정렬하면 다음과 같아야 합니다.

YEAR	MONTH	PUCHASED_USERS	PUCHASED_RATIO
2022	1	2	0.5
2022	2	1	0.3

 

 

정답 

 

SELECT YEAR(A.SALES_DATE) AS YEAR,MONTH(A.SALES_DATE) AS MONTH, COUNT(DISTINCT A.USER_ID) AS PUCHASED_USERS,
ROUND((COUNT(DISTINCT A.USER_ID) / (SELECT COUNT(*) FROM USER_INFO
WHERE JOINED LIKE '%2021%')),1) AS PUCHASED_RATIO FROM ONLINE_SALE AS A
INNER JOIN USER_INFO AS B
ON A.USER_ID=B.USER_ID
WHERE JOINED LIKE '%2021%'
GROUP BY YEAR,MONTH 
ORDER BY YEAR,MONTH

이 문제는 복잡해 보이지만 결과 테이블에서 PUCHASED_RATIO부분을 제외하면 나머지 열은 구하기 쉽다. 

 

PUCHASED_USERS열을 구하는 방법은 우선 공통행 USER_ID를 기준으로  두 테이블을 JOIN한 다음 WHERE절로 2021년에 가입한 사람만 뽑아 낸다. 

 

SELECT YEAR(A.SALES_DATE) AS YEAR,MONTH(A.SALES_DATE) AS MONTH, A.USER_ID  FROM ONLINE_SALE AS A
INNER JOIN USER_INFO AS B
ON A.USER_ID=B.USER_ID
WHERE JOINED LIKE '%2021%'

 위 코드의 결과 값은 아래와 같다.

YEAR	MONTH	USER_ID
2022	2	2
2022	1	2
2022	1	2
2022	1	4
2022	2	5
2022	1	7
2022	1	7
2022	1	12
2022	2	13
2022	2	14
2022	1	15
2022	1	15

·

·

·

 

PUCHASED_USERS는 21년에 상품을 구입한 회원의 수를 구하는 것이므로 COUNT(DISTINCT A.USER_ID)를 이용해 구하였다. 문제 해석이 헷갈려 DISTINCT의 사용여부가 애매할 수 있는데 예시 테이블을 자세히 참고바란다.

 

PUCHASED_RATIO는 PUCHASED_USERS/ 21년에 가입한 전체 회원수 인데 여기서 분모를 구한 방법은 아래와 같다.

SELECT COUNT(*) FROM USER_INFO
WHERE JOINED LIKE '%2021%'

위 코드의 결과값은 아래와 같다.

COUNT(*)

158

158이 21년에 가입한 전체 회원수이다. 여기서 숫자 158을 이용하는 것이 아닌 이 단일 테이블을 그대로 PUCHASED_RATIO의 분모로 이용하였다.  복잡한 코드로 추출해낸 단일 테이블은 하나의 숫자처럼 여겨질 수 있음을 알 게 되었다.

 

따라서 최종 정답 코드가 위와 같이 나왔고, 최종 정답 테이블은 다음과 같다.

YEAR	MONTH	PUCHASED_USERS	PUCHASED_RATIO
2022	1	47	0.3
2022	2	40	0.3
2022	3	6	0.0

 

 

 

 

*추가

PUCHASED_USERS와 PUCHASED_RATIO는 상품을 구매한 회원의 수에 관한 값들인데, 처음 게시한 코드에 오류가 있다.  COUNT(DISTINCT A.USER_ID)는 중복되지 않은 모든 유저의 수를 세는 것인데, 만약 SALES_AMOUNT가 0인 회원이 있다면 그 회원은 포함되어선 안된다. 문제에 주어진 테이블에는 SALES_AMOUNT가 0인 회원이 없어서 우연히 정답이였던 것이다. SALES_AMOUNT가 0인 회원을 제외하도록 수정한 코드는 아래와 같다.

SELECT YEAR(A.SALES_DATE) AS YEAR,MONTH(A.SALES_DATE) AS MONTH, COUNT(DISTINCT CASE WHEN A.SALES_AMOUNT > 0 THEN A.USER_ID END) AS PUCHASED_USERS,
ROUND((COUNT(DISTINCT CASE WHEN A.SALES_AMOUNT > 0 THEN A.USER_ID END) / (SELECT COUNT(*) FROM USER_INFO
WHERE JOINED LIKE '%2021%')),1) AS PUCHASED_RATIO FROM ONLINE_SALE AS A
INNER JOIN USER_INFO AS B
ON A.USER_ID=B.USER_ID
WHERE JOINED LIKE '%2021%'
GROUP BY YEAR,MONTH 
ORDER BY YEAR,MONTH