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

[level 5] 상품을 구매한 회원 비율 구하기 - 131534 

[문제 링크](https://school.programmers.co.kr/learn/courses/30/lessons/131534)

프로그래머스

---

1️⃣ 문제 정의

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

2️⃣ 문제 조건

• USER_INFO 👉 USER_ID, JOINED 조회
• ONLINE_SALE 👉 SALES_DATE 조회
• 2021년에 가입한 전체 회원들 수와 그들중 구매를 한 이력이 있는 회원 수를 구해야 한다.
• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력해야 한다.

3️⃣ 테이블

USER_INFO

ONLINE_SALE

 

4️⃣ 풀이 과정

1) 두 테이블의 연관 컬럼인 USER_ID를 이용해 INNER JOIN 한다.

SELECT *
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID

2) 판매 이력이 있는 회원들 중 가입 연도가 2021년인 회원들만 조회한다.

SELECT *
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'

3) 연, 월 별로 출력하기 위해 GROUP BY 하여 조회한다.

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(OS.SALES_DATE, '%m') AS MONTH,
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH

4) USER_ID의 중복을 제거 한 후 개수를 출력한다.

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(OS.SALES_DATE, '%m') AS MONTH,
       COUNT(DISTINCT UI.USER_ID) AS PRUCHASED_USERS,
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH

5) 2021년에 가입한 회원 중, 구매 이력이 있는 회원들의 비율을 계산하여 출력한다.

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(OS.SALES_DATE, '%m') AS MONTH,
       COUNT(DISTINCT UI.USER_ID) AS PRUCHASED_USERS,
       ROUND(COUNT(DISTINCT UI.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE '2021%'), 1) AS PURCHASED_RATIO
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH

6) 연, 월 순서로 정렬하여 출력한다.

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(OS.SALES_DATE, '%m') AS MONTH,
       COUNT(DISTINCT UI.USER_ID) AS PRUCHASED_USERS,
       ROUND(COUNT(DISTINCT UI.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE '2021%'), 1) AS PURCHASED_RATIO
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

 

5️⃣ 최종 제출 코드

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') AS YEAR,
       DATE_FORMAT(OS.SALES_DATE, '%m') AS MONTH,
       COUNT(DISTINCT UI.USER_ID) AS PRUCHASED_USERS,
       ROUND(COUNT(DISTINCT UI.USER_ID) / (SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE '2021%'), 1) AS PURCHASED_RATIO
FROM USER_INFO UI
JOIN ONLINE_SALE OS ON UI.USER_ID = OS.USER_ID
WHERE UI.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH