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

문제

📌 MySQL 로 풀이

 

📌 문제 링크 :

프로그래머스

 

📌 문제 설명 :

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

 

📌 테이블 :

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

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, 판매량, 판매일을 나타냅니다.

ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

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

 

---

풀이

추측) 

2021년에 가입한 사람수를 변수로 먼저 대입.

그리고 2021년에 가입한 사람들 리스트를 대상으로 온라인 주문을 join해서 일치하는 것을 조회.

년, 월 기준으로 그룹화한 다음에 같은 달에 똑같은 사람이 주문했을 경우를 생각해서 중복제거로 DISTINCT 적용하고

count로 주문회원수, 2021가입자 수 변수에서 count 비율을 구해 출력하면 될 듯!

 

쿼리) 

* 1차) 실패

SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');

SELECT DISTINCT
    YEAR(O.SALES_DATE) AS YEAR,
    MONTH(O.SALES_DATE) AS MONTH,
    COUNT(*) AS PUCHASED_USERS,
    ROUND(COUNT(*) / @CNT, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE AS O
INNER JOIN (SELECT *
            FROM USER_INFO
            WHERE YEAR(JOINED) = '2021') AS U
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC

* 2차) 성공

SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');

SELECT 
    YEAR, 
    MONTH, 
    COUNT(*) AS PUCHASED_USERS,
    ROUND(COUNT(*)/@CNT, 1) AS PUCHASED_RATIO
FROM (SELECT DISTINCT
        YEAR(SALES_DATE) AS YEAR,
        MONTH(SALES_DATE) AS MONTH,
        USER_ID
      FROM ONLINE_SALE) AS O                   # 년,달마다 구매한 회원 리스트  
INNER JOIN (SELECT USER_ID
            FROM USER_INFO
            WHERE YEAR(JOINED) = '2021') AS U  # 2021년에 가입한 회원 리스트
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC

 

리뷰) 

1차에서 중복제거랑 그룹화를 같이하면서 의도치 않은 결과가 나왔다. 

DISTINCT가 실행되는 기준을 생각해보면

1차에서는 중복이 제거되지 않은 상태로 먼저 count과 비율이 다 계산되고난 이후에 중복을 제거하는 거라,

같은 달에 또 구매한 구매자를 거르지 못했다.

 

이 중복을 먼저 제거하기 위해 

2차에서는 년과 달 기준으로 구매한 회원을 중복없이 리스트화 시킬 수 있게 질의해서 먼저 정리하고 

이를 기준으로 count해서 정확한 결과가 나오게 했다!