[프로그래머스 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;

 

 

 

 

결과

 

 

 

 

---

🖍 오답노트

틀린 이유

내가 풀이한 쿼리에서 어디가 틀렸는지 모르겠어서 나의 스승님(=챗 지피티)께 물어봤다. (일부 틀린 해설을 주시긴 했지만..;;)

 

1. 서브쿼리의 날짜 포맷 문제
   `DATE_FORMAT` 함수를 사용하여 날짜를 포맷팅하고 있지만, 이 함수는 포맷된 문자열을 반환한다.
   → `JOINED LIKE '2021%'` 조건을 사용하여 2021년에 가입한 회원을 필터링하는 것이 더 간단하다.
   
   
2. 비율 계산 위치
   비율 계산은 `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

 

 

 

[풀이3 참고]

https://jaehwaseo.tistory.com/8

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

 

 

 

[풀이4 참고]

https://velog.io/@gayeong39/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-%EC%83%81%ED%92%88%EC%9D%84-%EA%B5%AC%EB%A7%A4%ED%95%9C-%ED%9A%8C%EC%9B%90-%EB%B9%84%EC%9C%A8-%EA%B5%AC%ED%95%98%EA%B8%B0