[프로그래머스] 상품을 구매한 회원 비율 구하기 (23.06.13 풀이 추가)

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는

       프로그래머스 코딩테스트 연습,   
       https://school.programmers.co.kr/learn/challenges?page=1&languages=mysql%2Coracle임을 밝힙니다.

---

■ 문제

문제 설명

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_ SALE 테이블 입니다.

USER_INFO 테이블은 아래와 같은 구조로 되어있며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

 

Column name	Type	Nullable
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, 판매량, 판매일을 나타냅니다.

Column name	Type	Nullable
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문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

---

 

■ 풀이

  문제 요구사항  

USER_INFO 테이블과 ONLINE_SALE 테이블에서

2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율

(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력

 

> 비율의 분모, 분자 데이터 수량에 모두 '2021년에 가입한' 회원이라는 조건이 부여되었다.

   이 조건을 쿼리로 잘 풀어내는 것이 중요했다.

         <분모> 는 (2021년 가입자이면서) ONLINE_SALE 테이블에서 2021년 구매 회원의 수를 추출해야하고

          <분자> 는 USER_INFO 테이블에서 2021년 가입 회원의 수를 추출.

 

> 쿼리 실행 순서를 생각했을 때,  ONLINE_SALE 테이블 데이터에서  USER_INFO에서 2021년 가입 회원만을 대상으로 테이블 데이터를 구하기 위해 USER_INFO 테이블에 JOINED 날짜 기간 조건 +  INNER JOIN 적용 

 

> WHERE절에 가입시기 조건은 이전에 풀었던 문제처럼 DATE_FORMAT() 아닌 BETWEEN ~ AND 문법 사용

 

> 분모에 USER_INFO 테이블을 대상으로 2021년 가입자 수만 뽑아주는 서브쿼리를 사용한 것이 풀이에 제일 시작점이 되었던 것 같다. 

 

상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림   > SELECT절에서 ROUND() 함수 사용

전체 결과는 년을 기준으로 오름차순 정렬                                     > 정렬조건 1

년이 같다면 월을 기준으로 오름차순 정렬                                     > 정렬조건 2

 

SELECT DATE_FORMAT(OS.SALES_DATE, '%Y') YEAR, 
DATE_FORMAT(OS.SALES_DATE, '%m') MONTH,
COUNT(DISTINCT OS.USER_ID) PUCHASED_USERS
,ROUND(COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) 
                                     FROM USER_INFO
                                     WHERE JOINED BETWEEN '2021-01-01 00:00:00'
                                     AND '2021-12-31 23:59:59'), 1) PUCHASED_RATIO
 FROM ONLINE_SALE OS
 INNER JOIN (SELECT USER_ID, JOINED 
            FROM USER_INFO
            WHERE JOINED BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59') UI
 ON OS.USER_ID = UI.USER_ID
 GROUP BY 1, 2
 ORDER BY 1, 2

 

✔ 추가 : 날짜함수 활용해서 코드 간단히 하기 (2023.06.13 풀이)

SELECT YEAR(OS.SALES_DATE) AS YEAR
      ,MONTH(OS.SALES_DATE) AS MONTH
      ,COUNT(DISTINCT OS.USER_ID) AS PUCHASED_USERS
      ,ROUND((COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(DISTINCT USER_ID)
                                            FROM USER_INFO
                                            WHERE YEAR(JOINED) = '2021')), 1) AS PUCHASED_RATIO
 FROM ONLINE_SALE OS
 JOIN USER_INFO UI
 ON OS.USER_ID = UI.USER_ID AND YEAR(UI.JOINED) = '2021'
 GROUP BY 1, 2
 ORDER BY 1, 2;

추가 풀이에서는 조인할 테이블에 user_info에서 가져올 때 가입년도 = 2021 조건을 걸어주는 대신

조인 과정에서 가입년도가 2021년인 user의 user_id만 조인되도록 해봤다.