[MySQL] 프로그래머스 - 상품을 구매한 회원 비율 구하기 (DATE, ROUND)

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

프로그래머스

 

 

문제

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

---

 

구현 과정

• 2021년에 가입한 회원 정보와 해당 회원들의 상품 판매 정보 구하기 
• (위 과정에서 구한) 상품 판매 정보를 년,월 별로 그룹화 하여 상품을 구매한 회원수, 회원의 비율 구하기 

 

1️⃣ 2021년에 가입한 회원 정보와 해당 회원들의 상품 판매 정보 구하기 

2021년에 가입한 회원 정보와 해당 회원들의 상품 판매 리스트를 구하기 위해서는 USER_INFO 테이블과 ONLINE_SALE 테이블을 JOIN 한 후, WHERE 절로 가입 날짜의 조건을 추가하여야 한다. 

• USER_INFO : 쇼핑몰에 가입한 회원 정보를 담은 테이블 (JOINED : 가입날짜) 
• ONLINE_SALE : 온라인 상품 판매 정보를 담은 테이블 (USER_ID별로 상품 판매 정보 포함) 

 

두 테이블을 USER_ID 컬럼을 기준으로 INNER JOIN  하면, 각 회원들의 가입정보와 판매정보를 구할 수 있다. 이후 WHERE절에 JOINED 의 YEAR() = 2021인 조건을 추가하면 2021에 가입한 회원들의 정보만 확인할 수 있다. 

• YEAR(컬럼) : DATETIME 컬럼의 년도 정보만 추출 

 

SELECT *
FROM USER_INFO A 
JOIN ONLINE_SALE B 
ON A.USER_ID  =B.USER_ID 
WHERE YEAR(A.JOINED) = '2021' ;

 

2️⃣ (위 과정에서 구한) 상품 판매 정보를 년,월 별로 그룹화 하여 상품을 구매한 회원수, 회원의 비율 구하기 

2021년에 가입한 회원의 정보와 판매 정보를 구했다면, 판매 날짜의 년,월 별로 그룹화 한다. 

• YEAR(컬럼) : DATETIME 컬럼의 년도 정보만 추출
• MONTH(컬럼) : DATETIME 컬럼의 월 정보만 추출 

 

YEAR, MONTH 함수를 사용하여, SALES_DATE 컬럼의 년, 월 정보를 추출한다. 두 개의 컬럼을 기준으로 그룹화 하기 위해 GROUP BY 절에 콤마(,) 로 나열한다. 

 

그룹화 한 후, 그룹별로 상품을 구매한 회원수와 회원의 비율을 구하는데 ,

👉 상품을 구매한 회원수 : 동일한 회원이 같은 년도, 월에 여러번 구매했을 수 있으므로 DISTINCT를 사용하여 중복을 제거한 후 COUNT 한다. 

👉 회원의 비율 : 서브쿼리를 사용하여 USER_INFO 테이블에서 2021년에 가입한 회원의 수 (COUNT) 만 불러와 (상품을 구매한 회원수)를 나누어주었다. 단, 소수점 둘째자리에서 반올림 하기 위해 ROUND(값, 1) 함수 사용 

SELECT YEAR(B.SALES_DATE) AS YEAR, MONTH(B.SALES_DATE) AS MONTH, 
       COUNT(DISTINCT(A.USER_ID)) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT(A.USER_ID))/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) ='2021'),1) 
       AS PUCHASED_RATIO
FROM USER_INFO A 
JOIN ONLINE_SALE B 
ON A.USER_ID  =B.USER_ID 
WHERE YEAR(A.JOINED) = '2021' 
GROUP BY YEAR, MONTH

 

---

✔️코드

SELECT YEAR(B.SALES_DATE) AS YEAR, 
       MONTH(B.SALES_DATE) AS MONTH, COUNT(DISTINCT(A.USER_ID)) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT(A.USER_ID))/(SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) ='2021'),1) 
       AS PUCHASED_RATIO
FROM USER_INFO A 
JOIN ONLINE_SALE B 
ON A.USER_ID  =B.USER_ID 
WHERE YEAR(A.JOINED) = '2021' 
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

💡ORDER BY 기준이 여러개일 경우, 우선순위에 맞춰 콤마(,) 로 나열