[프로그래머스 SQL 코딩테스트 연습] Lv5. 상품을 구매한 회원 비율 구하기 (MySQL)
🤔 문제
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 `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문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
예시
예를 들어 `USER_INFO` 테이블이 다음과 같고
`ONLINE_SALE` 이 다음과 같다면
2021년에 가입한 회원은 `USER_ID`가 1, 2, 3, 4 인 회원들로 총 4명 입니다. `ONLINE_SALE` 테이블에서 해당 회원들에 대한 판매 데이터는 다음과 같습니다.
그러므로 년, 월 별로 상품을 구매한 회원수와 상품을 구매한 회원의 비율을 구하고 결과를 정렬하면 다음과 같아야 합니다.
💻 나의 풀이
-- 21년도에 가입한 회원 정보
WITH JOINERS AS(
SELECT USER_ID, DATE_FORMAT(JOINED, '%Y-%m-%d') AS JOINED
FROM USER_INFO
WHERE JOINED LIKE '2021%'
)
-- 21년도에 가입한 회원들 중, 상품 구매 이력이 있는 회원의 구매 정보
SELECT
YEAR(s.SALES_DATE) AS YEAR,
MONTH(s.SALES_DATE) AS MONTH,
COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS
ROUND(COUNT(DISTINCT s.USER_ID)/(SELECT COUNT(*) FROM JOINERS), 1) AS PUCHASED_RATIO
FROM ONLINE_SALE s
JOIN JOINERS j ON s.USER_ID=j.USER_ID
GROUP BY 1, 2
ORDER BY 1, 2;
결과
🖍 오답노트
틀린 이유
내가 풀이한 쿼리에서 어디가 틀렸는지 모르겠어서 나의 스승님(=챗 지피티)께 물어봤다. (일부 틀린 해설을 주시긴 했지만..;;)
- 서브쿼리의 날짜 포맷 문제
`DATE_FORMAT` 함수를 사용하여 날짜를 포맷팅하고 있지만, 이 함수는 포맷된 문자열을 반환한다.
→ `JOINED LIKE '2021%'` 조건을 사용하여 2021년에 가입한 회원을 필터링하는 것이 더 간단하다. - 비율 계산 위치
비율 계산은 `WITH` 절 내부가 아닌 메인 쿼리에서 수행되어야 한다.
정답 풀이
1. 구해야 하는 비율의 분자, 분모에 대해 각각 CTE를 생성한 풀이
-- 21년도에 가입한 회원 정보
WITH JOINERS AS (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = 2021
),
-- 2021년에 가입한 회원들 중, 상품 구매 이력이 있는 회원의 구매 정보
PURCHASES AS (
SELECT YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
USER_ID
FROM ONLINE_SALE
WHERE USER_ID IN (SELECT USER_ID FROM JOINERS)
)
-- 구매한 회원 수와 비율 계산
SELECT
p.YEAR,
p.MONTH,
COUNT(DISTINCT p.USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT p.USER_ID) / (SELECT COUNT(*) FROM JOINERS), 1) AS PURCHASED_RATIO
FROM PURCHASES p
GROUP BY 1, 2
ORDER BY 1, 2;
2. CTE , JOIN없이 WHERE절 안에 서브쿼리로만 작성한 풀이
SELECT
YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
COUNT(DISTINCT USER_ID) AS PURCHASED_USERS,
ROUND(COUNT(DISTINCT USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED)=2021),1) AS PUCHASED_RATIO
FROM ONLINE_SALE
WHERE USER_ID IN (SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED)=2021)
GROUP BY 1, 2
ORDER BY 1, 2;- `COUNT(DISTINCT USER_ID)` : `COUNT(*)`을 사용할 경우 여러번 구매한 사람이 중복 카운트 되므로 중복 제거한다.
3. JOIN을 활용한 풀이
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 1, 2
ORDER BY 1, 2;이게 가장 간단하고 깔끔한 풀이인 것 같다.
- `JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID WHERE U.JOINED LIKE '2021%'`
: 구매 이력이 있는 2021년에 가입한 회원들만 필터링 - 이 때, `JOIN`된 테이블 결과는 한 회원에 대해서 여러 번 구매 이력이 존재할 수 있기 때문에 `DISTINCT`로 중복을 제거한다.
4. FROM절 안에 ONLINE_SALE, USER_ID 두 테이블을 조인하는 풀이
SELECT A.YEAR AS YEAR,
A.MONTH AS MONTH,
COUNT(*) AS PUCHASED_USERS,
ROUND((COUNT(*)/ (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM ( -- 두테이블을 조합하여 새로운 테이블 만든다.
SELECT DISTINCT -- YEAR, MONTH, USER_ID 조합이 중복이 없도록
YEAR(S.SALES_DATE) AS YEAR, -- 연도만 따로 뺀다
MONTH(S.SALES_DATE) AS MONTH,-- 월만 따로 뺀다
U.USER_ID
FROM ONLINE_SALE S
JOIN USER_INFO U ON S.USER_ID = U.USER_ID
AND YEAR(U.JOINED) = 2021 -- 가입한 년도가 2021년인
) A
GROUP BY 1, 2
ORDER BY 1, 2;
🔗 References
[풀이2 참고]
https://chaemi720.tistory.com/239
[프로그래머스] 상품을 구매한 회원 비율 구하기 - MySQL
https://school.programmers.co.kr/learn/courses/30/lessons/131534 프로그래머스 코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞
chaemi720.tistory.com
[풀이3 참고]
https://jaehwaseo.tistory.com/8
프로그래머스 상품을 구매한 회원 비율 구하기 SQL(MySQL)
문제 다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER,
jaehwaseo.tistory.com
[풀이4 참고]