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

Question

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 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 테이블이 다음과 같고

USER_ID	GENDER	AGE	JOINED
1	1	26	2021-06-01
2	NULL	NULL	2021-06-25
3	0	NULL	2021-06-30
4	0	31	2021-07-03
5	1	25	2022-01-09
6	1	33	2022-02-14

ONLINE_SALE 이 다음과 같다면

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
4	6	253	3	2022-02-03
5	2	31	2	2022-02-09
6	5	35	1	2022-02-14
7	5	57	1	2022-02-18

2021년에 가입한 회원은 USER_ID가 1, 2, 3, 4 인 회원들로 총 4명 입니다. ONLINE_SALE 테이블에서 해당 회원들에 대한 판매 데이터는 다음과 같습니다.

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
5	2	31	2	2022-02-09

그러므로 년, 월 별로 상품을 구매한 회원수와 상품을 구매한 회원의 비율을 구하고 결과를 정렬하면 다음과 같아야 합니다.

YEAR	MONTH	PUCHASED_USERS	PUCHASED_RATIO
2022	1	2	0.5
2022	2	1	0.3

---

Solve

필자의 풀이 순서

1. 2021년도에 가입한 회원의 전체 수를  파악했다.

2. 2021년도에 가입한 회원 중 실제 구매한 고객의, 구매 년도와 월별 구매 회원수를 파악했다.

3. 두 테이블을 함께 호출하여 구매 연, 월을 집계 기준으로 구매 고객 수와 구매 고객 비율을 파악했다.

4. 비율은 소숫점 둘째 자리에서 반올림하고 연, 월을 오름차순으로 정렬했다. 

select y, m, b.cnt as PUCHASED_USERS, round(b.cnt/ a.cnt, 1) as PUCHASED_RATIO
from (select count(distinct USER_ID) as cnt 
      from user_info 
      where year(joined) = 2021) as a 
      # 2021년에 가입한 회원 수
     ,(select year(SALES_DATE) as y, month(SALES_DATE) as m, count(distinct os.USER_ID)  as cnt 
      from online_sale as os 
      left join user_info as ui 
      on os.user_id = ui.user_id 
      where year(ui.joined) = 2021
      group by y, m) as b 
      # 2021년에 가입, 상품을 구매한 회원
order by y, m

필자는 위의 코드를 작성

뽑아오는 테이블 단에서 조건이나, 집계를 마무리한 후 필요한 컬럼들만 추출하는 코드를 작성했다.

select year(SALES_DATE) as y, month(SALES_DATE) as m
    , 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 as os 
      left join user_info as ui 
      on os.user_id = ui.user_id 
where year(ui.joined) = 2021
group by y, m
order by y, m

이외에도 추출단을 간락하게 하여 코드를 간결하게 작성하는 방법도 가능하다.

---

Source

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

프로그래머스