프로그래머스 상품을 구매한 회원 비율 구하기 SQL(MySQL)

문제

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 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 조합에 대해서는 하나의 판매 데이터만 존재합니다.

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

 

---

개념 공부하기

GROUP BY, COUNT	GROUP 그룹화 후 COUNT 세기
JOIN, INNER JOIN	JOIN : 두 테이블 합치기

 

풀이

문제를 읽으면서 조건들을 분리하며 이해해 본다.

2021년 가입한 정체 회원, 상품 구매 이력이 있는 회원,

상품 구매 회원의 비율 ( 2021 가입, 구매 이력 회원 / 2021가입, 전체 회원)

그리고 이것들을 년, 월 별로 출력을 해야 하니 그룹을 묶어줘야겠다.

1. 우선 user table과 online table을 user_id로 join을 시킨 뒤 where절로 2021년 가입한 회원들만 추려준다.
2. 문제에서 원하는 년도와 월은 DATE_FORMAT()을 이용하여 추출한 뒤 column명을 year, month로 지정하여 나중에 group by (column명)을 깔끔하게 해 준다.
3. 년, 월별로 그룹을 묶어준 뒤 해당 월에 구입한 회원 총 수를 count()를 이용해 구해준다.
4. (주의) join 된 테이블 결과는 한 회원이 여러 번 구매 이력이 있을 수 있기 때문에 distinct를 이용하여 중복 회원 id를 제거해 준 뒤 count를 세줘야 한다.
5. 상품 구매 회원 비율을 구하기 위해 2021년에 가입한 총 회원 수를 구해줘야 된다.
6. (SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%') 서브 쿼리를 이용해 간단하게 구해준다.
7. 문제에서 둘째 자리에서 반올림을 해줘야 하기 때문에 round() 이용해 준다.
8. 문제에서 원하는 대로 order by

Round() 함수에서 ex) round(0.1234, 2)는 소수점 둘째 자리까지 반올림을 하는 의미이다. 문제에서는 소수점 둘째 자리에서 반올림을 해야 하기 때문에, 첫째 자리까지 반올림한다는 의미로 이해할 수 있다.

 

SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
        DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
        COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%'), 1) AS PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH
;

 

---

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

프로그래머스