문제 설명
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 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 | INTEGER | 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문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
풀이
개인적으로 레벨 5답게 여태껏 풀었던 문제중에 가장 어려웠다
상품 구매 회원 비율을 년, 월 별로 출력하는 과정에서 감을 잡기가 어려웠는데
조인한 테이블은 사실상 구매 이력이 있는 회원들의 정보만 가지고 있기 때문에 2021년에 가입한 전체 회원의 수를 알기 위해서는 서브쿼리를 사용해야한다는 것을 잊고 있었다
그리고 한 회원이 여러번 구매를 할 수 있기 때문에 중복 제거를 해줘야하는 것도 고려하지 않았었다
SELECT
YEAR(OS.SALES_DATE) AS YEAR,
MONTH(OS.SALES_DATE) AS MONTH,
COUNT(DISTINCT OS.USER_ID) AS PUCHASED_USERS,
ROUND(COUNT(DISTINCT UI.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = "2021"),1) AS PUCHASED_RATIO
FROM USER_INFO AS UI
LEFT OUTER JOIN ONLINE_SALE AS OS
ON UI.USER_ID = OS.USER_ID
WHERE YEAR(UI.JOINED) = "2021" AND OS.USER_ID IS NOT NULL
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH