[프로그래머스] - 상품을 구매한 회원 비율 구하기

문제 설명

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 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문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

예시

 

 

문제 풀이

1. ONLINE_SALE 테이블과 USER_INFO 테이블 조인하기(LEFT JOIN)

 

첫번째 단계에서는 2개의 테이블을 조인하였다.

한명의 회원이 여러 상품을 구매할수 있으니, ONLINE_SALE 테이블을 기준으로 USER_INFO 테이블과 USER_ID를 공통 컬럼으로 하여 LEFT_JOIN을 한다.

SELECT *
FROM ONLINE_SALE AS A
LEFT JOIN USER_INFO AS B
ON A.USER_ID = B.USER_ID

 

2. 2021년에 가입한 회원 데이터 필터링(WHERE)

 

두번째 작업은 WHERE 절에서 조건에 맞는 데이터만 조회가 되도록 조건 필터링을 걸었다.

2021년에 가입한 회원만을 가져오면 되기 때문에, 연도가 2021년인 것을 데이터를 가져왔다.

 

조건: 2021년에 가입한 회원 데이터(YEAR(JOINED) = '2021')

LIKE문을 이용해서 조회해도 되지만, 년도만 이용하는것이기 때문에 YEAR()를 이용했다.

SELECT *
FROM ONLINE_SALE AS A
LEFT JOIN USER_INFO B
ON A.USER_ID = B.USER_ID
WHERE YEAR(B.JOINED) = '2021'

 

3. 집계해주기(GROUP BY)

 

세번째 작업은 GROUP BY문을 이용해서 집계할 컬럼을 정의했다.

문제에서 요구한것과 동일하게 연도와 월 기준으로 묶었다.

SELECT YEAR(A.SALES_DATE) AS YEAR,
	  MONTH(A.SALES_DATE) AS MONTH,
      *
FROM ONLINE_SALE AS A
LEFT JOIN USER_INFO AS B
ON A.USER_ID = B.USER_ID
WHERE YEAR(B.JOINED)='2021'
GROUP BY YEAR(A.SALES_DATE),MONTH(A.SALES_DATE)

 

4. 구매한 회원수와 상품을 구매한 회원의 비율을 구하기

 

이 단계에서는 집계한 컬럼에 해당하는 구매한 회원수와 상품을 구매한 회원의 비율을 구했다.

회원 수를 구하는 것은 COUNT(DISTINCT(USER_ID))를 통해

고유한 회원수를 쉽게 산출했다.

하지만 구매한 회원의 비율을 구하기 위해서는 전체의 회원수를 이용해서 산출해야 한다.

이미 구성한 쿼리에는 GROUP BY를 통해 데이터를 나눠두었기 때문에,

새로운 쿼리를 통해 2021년에 가입한 고객의 수를 COUNT()를 이용해 구했다.

또한 소수점 두 번째 자리에서 반올림을 하도록 ROUND()함수를 써줬다.

SELECT YEAR(A.SALES_DATE) AS YEAR,
		MONTH(A.SALES_DATE) AS MONTH,
        COUNT(DISTINCT(B.USER_ID)) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT B.USER_ID)/(SELECT COUNT(USER_ID) FROM
 USER_INFO WHERE YEAR(JOINED)='2021'),1)AS PURCHASED_RATIO
 FROM ONLINE_SALE AS A
 LEFT JOIN USER_INFO AS B
 ON A.USER_ID = B.USER_ID
 WHERE YEAR(B.JOINED) = '2021'
 GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE)

5. '년' 기준으로 오름차순 정렬, '월' 기준으로 오름차순 정렬

 

마지막으로 년(YEAR)과 월(MONTH) 기준으로 오름차순 정렬

SELECT YEAR(A.SALES_DATE) AS YEAR,
		MONTH(A.SALES_DATE) AS MONTH,
        COUNT(DISTINCT(B.USER_ID)) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT B.USER_ID)/(SELECT COUNT(USER_ID) FROM
 USER_INFO WHERE YEAR(JOINED)='2021'),1)AS PURCHASED_RATIO
 FROM ONLINE_SALE AS A
 LEFT JOIN USER_INFO AS B
 ON A.USER_ID = B.USER_ID
 WHERE YEAR(B.JOINED) = '2021'
 GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE)
 ORDER BY YEAR ASC, MONTH ASC

 

난이도 5단계는 처음풀어본거같다

이 문제는 2개의 테이블을 JOIN하고 GROUP BY를 통해 집계하는 과정에서 서브쿼리와 비슷한 작업을 통해 원하는 값을 불러오는 것을 알려주고 싶었던 문제로 추정된다.

문제를 풀이할수록 1~5단계 전부 유사한 쿼리가 반복된다는것이 느껴진다.