프로그래머스(programmers) - LV. 5 상품을 구매한 회원 비율 구하기

 

 

프로그래머스는 hackerrank보다 전반적으로 쉽다고 하여, LV. 5부터 역순으로 풀어보려고 했다.
건드려 보니 안풀어봐도 될 것 같다는 생각이 들었다.

 

Problem

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

Column name	Type	Nullable
USER_ID	INTEGER	FALSE
GENDER	TINYINT(1)	TRUE
AGE	INTERGER	TRUE
JOINED	DATE	FALSE

 

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

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

Column name	Type	Nullable
ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

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

 

 

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

 

---

Answer1

SELECT YEAR(OS.SALES_DATE) AS YEAR
      ,MONTH(OS.SALES_DATE) AS MONTH
      ,COUNT(DISTINCT OS.USER_ID) AS PUCHASED_USERS
      ,ROUND(COUNT(DISTINCT OS.USER_ID)
       /(SELECT COUNT(UI.USER_ID) FROM USER_INFO UI WHERE YEAR(UI.JOINED) = '2021'),1) AS PUCHASED_RATIO
  FROM (SELECT UI.USER_ID
          FROM USER_INFO UI
         WHERE YEAR(UI.JOINED) = '2021') UI 
               LEFT JOIN ONLINE_SALE OS ON OS.USER_ID = UI.USER_ID
 WHERE YEAR(OS.SALES_DATE) IS NOT NULL
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

 

---

How to solve

유저 테이블(USER_INFO)와 판매 테이블(ONLINE_SALE)에서 JOIN을 활용해 간단한 집계를 하는 문제이다.

 

먼저 가장 크게 필터링 해야할 연도 기준의 회원을 기준으로 FROM절에서 서브쿼리를 하나 만들어준다.

SELECT UI.USER_ID
  FROM USER_INFO UI
 WHERE YEAR(UI.JOINED) = '2021'

 

그 다음 추출된 USER_ID를 이용해 판매 테이블과 LEFT JOIN으로 묶어준다.

 

 

 

SELECT절에서는 년, 월, 구매 유저의 ID별 출력을 위해 컬럼을 만들어주고, 구매비율 컬럼까지 만들어준다.

SELECT YEAR(OS.SALES_DATE) AS YEAR
      ,MONTH(OS.SALES_DATE) AS MONTH
      ,COUNT(DISTINCT OS.USER_ID) AS PUCHASED_USERS
      ,ROUND(COUNT(DISTINCT OS.USER_ID)/(SELECT COUNT(UI.USER_ID) FROM USER_INFO UI WHERE YEAR(UI.JOINED) = '2021'),1) AS PUCHASED_RATIO
FROM (SELECT UI.USER_ID
      FROM USER_INFO UI
     WHERE YEAR(UI.JOINED) = '2021') UI 
           LEFT JOIN ONLINE_SALE OS ON OS.USER_ID = UI.USER_ID

 

여기서 한 번도 구매를 하지 않은 유저가 존재할 수 있기 때문에,

WHERE절에는 약간의 꼼수를 써서, NULL값이 아닌 값만 데리고 온다.

 WHERE YEAR(OS.SALES_DATE) IS NOT NULL

 

 

 

출처

프로그래머스 - https://school.programmers.co.kr/learn/courses/30/lessons/131534#