프로그래머스 SQL 풀이 - 상품을 구매한 회원 비율 구하기

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

프로그래머스

 

문제 설명

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

 

출처 : 프로그래머스

 

GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.

 

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며 ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.

 

출처 : 프로그래머스

 

동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

 

문제

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

 

결과 예시

출처 : 프로그래머스

 

풀이

이 문제는 Level 5 치고는 쉬운 편이었다.

헷갈릴 만한 부분이 하나 있는데 GROUP BY의 컬럼만 적절하게 잡아주면 메인 쿼리 하나로 끝난다.

 

1. 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수 as PUCHASED_USERS

 

 - 즉, USER_INFO에 존재하는 회원 중 ONLINE_SALE 테이블에 존재하는 회원인지 묻는 것이다.

   => JOIN을 하라는 의미이며, WHERE 절에 YEAR() 함수로 필터링하면 된다.

 

 - 그런데, GROUP BY를 USER_ID로 잡고 COUNT 집계를 하게 되면 정답과 개수 오차가 발생한다.

   => 왜냐하면, 문제에서 년, 월별로 집계를 요구하고 있고, 결과 예시를 보면 회원별 데이터가 아니다. 따라서, GROUP BY의 컬럼은 년(YEAR), 월(MONTH)이 되어야 한다.

   => COUNT 집계 시, 회원의 수를 세어야 하므로, 컬럼을 USER_ID로 하고 한 회원이 여러 번 구매할 수 있으므로 DISTINCT해야 한다.

 

2. 2021년에 가입한 전체 회원 수

 

 - USER_INFO에서 년도만 필터링하면 되므로, 스칼라 쿼리로 셀렉트절에 넣도록 한다.

 

3. (1번 / 2번) 비율 as PUCHASED_RATIO

 

 - 1번처럼 COUNT(DISTINCT USER_ID)를 하여 회원 별로 카운팅한 값에 2번을 나누면 된다.

 - 소수점 두 번째 자리에서 반올림해야 하므로 ROUND() 함수를 사용하고 자릿수에 1을 주면 된다.

 

정답 코드

SELECT YEAR(b.SALES_DATE) as YEAR, MONTH(b.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 USER_INFO a
    JOIN ONLINE_SALE b ON a.USER_ID = b.USER_ID
WHERE YEAR(a.JOINED) = '2021'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH