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

 

안녕하세요! 데코입니다!

 

오늘은 프로그래머스 코딩테스트 연습에 있는 "상품을 구매한 회원 비율 구하기" 문제를 포스팅하려고 합니다!

 

바로 포스팅 시작할게요! :)

 

(출처 : https://school.programmers.co.kr/learn/courses/30/lessons/131534)

---

 

 

1. 문제 :  상품을 구매한 회원 비율 구하기(Lv. 5)

 

 

---

 

2. 문제 설명

 

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과

온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블입니다.

USER_INFO 테이블은 아래와 같은 구조로 되어있으며

USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

 

[ 테이블 구조('USER_INFO') ]

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

 

[ 테이블 구조('ONLINE_SALE') ]

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_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 ]

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SAELS_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	SAELS_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

 

---

 

3. 문제 풀이

-- 1안
SELECT  YEAR(A.SALES_DATE) AS YEAR
        , MONTH(A.SALES_DATE) AS MONTH
        , COUNT(DISTINCT(B.USER_ID)) AS PUCHASED_USERS
        , ROUND(COUNT(DISTINCT B.USER_ID)/(SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED)='2021'), 1)
    AS PURCHASED_RATIO
  FROM  ONLINE_SALE AS A
  LEFT
  JOIN  USER_INFO AS B
    ON  A.USER_ID = B.USER_ID
 WHERE  YEAR(B.JOINED)='2021'
 GROUP
    BY  YEAR(A.SALES_DATE)
        , MONTH(A.SALES_DATE)
 ORDER
    BY  YEAR ASC
        , MONTH ASC

이번 문제는 1안 한 가지로 작성했습니다!

 

#  1안 풀이 설명

# [ 문제 접근 설명 ]

문제를 읽고 조건에 맞는 쿼리를 작성하기 위해서 다음과 같이  다섯 단계로 진행했어요!

1. ONLINE_SALE 테이블과 USER_INFO 테이블 조인하기(LEFT JOIN)

2. 2021년에 가입한 회원 데이터 필터링(WHERE)
3. 집계해주기(GROUP BY)

4. 구매한 회원수와 상품을 구매한 회원의 비율을 구하기

5. '년' 기준으로 오름차순 정렬, '월' 기준으로 오름차순 정렬

 

# [ 1안 쿼리 작성 설명 ]

 

 1  첫 번째 단계에서는 2개의 테이블을 조인하였습니다!

 

한 명의 회원이 여러 상품을 구매할 수 있으니, 

 

ONLINE_SALE 테이블을 기준으로 USER_INFO 테이블과 USER_ID를 공통 컬럼으로 하여 LEFT_JOIN 합니다!

 

SELECT   *
   FROM   ONLINE_SALE AS A
     LEFT
     JOIN   USER_INFO AS B
        ON   A.USER_ID = B.USER_ID

 

 2  두 번째 작업은 WHERE절에서 조건에 맞는 데이터만 조회가 되도록

 

조건 필터링을 걸어주었습니다!

 

2021년에 가입한 회원만을 가져오면 되기 때문에, 연도가 2021년인 것을 데이터를 가져왔습니다!

 

조건 : 2021년에 가입한 회원 데이터 ( YEAR(JOINED) = '2021' )

 

LIKE문을 이용해서 조회해도 되지만, 년도만 이용하는 것이기 때문에 YEAR()를 이용했습니다!

 

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

 

 3  세 번째 작업은 GROUP BY 문을 이용해서 집계할 컬럼을 정의했습니다!

 

문제에서 요구한 것과 동일하게 연도와 월 기준으로 묶어서 보여주었어요!

 

SELECT   YEAR(A.SALES_DATE) AS YEAR
                 , MONTH(A.SALES_DATE) AS MONTH
                 , *
   FROM   ONLINE_SALE AS A
     LEFT
     JOIN   USER_INFO AS B
        ON   A.USER_ID = B.USER_ID
WHERE  YEAR(B.JOINED) = '2021'
GROUP
         BY  YEAR(A.SALES_DATE)
                , MONTH(A.SALES_DATE)

 

 4  이 단계에서는 집계한 컬럼에 해당하는 구매한 회원수와 상품을 구매한 회원의 비율을 구했습니다!

 

회원 수를 구하는 것은 COUNT(DISTINCT(USER_ID))를 통해

 

고유한 회원수를 쉽게 산출했습니다!

 

하지만 구매한 회원의 비율을 구하기 위해서는 전체의 회원 수를 이용해서 산출해야합니다!

 

이미 구성한 쿼리에는 GROUP BY를 통해 데이터를 나눠두었기 때문에,

 

새로운 쿼리를 통해 2021년에 가입한 고객의 수를 COUNT()를 이용해 구했습니다!

 

또한 소수점 두 번째 자리에서 반올림을 하도록 ROUND() 함수를 써주었어요!

 

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

 

 

 5  마지막으로는 요구사항에 맞게 정렬했어요!

년(YEAR)과 월(MONTH) 기준으로 오름차순 정렬했습니다!

 

SELECT   YEAR(A.SALES_DATE) AS YEAR
                 , MONTH(A.SALES_DATE) AS MONTH
                 , COUNT(DISTINCT(B.USER_ID)) AS PUCHASED_USERS
                 , ROUND(COUNT(DISTINCT B.USER_ID)/(SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED)='2021'), 1) AS PURCHASED_RATIO
   FROM   ONLINE_SALE AS A
     LEFT
     JOIN   USER_INFO AS B
        ON   A.USER_ID = B.USER_ID
WHERE  YEAR(B.JOINED) = '2021'
GROUP
         BY  YEAR(A.SALES_DATE)
                , MONTH(A.SALES_DATE)
ORDER
        BY  YEAR ASC
               , MONTH ASC

 

정답...!

---

 

4. 데코의 문제 풀이 후기

난이도 5단계의 문제는 처음 풀어본 것 같습니다!

 

이 문제는 2개의 테이블을 JOIN 하고 GROUP BY를 통해 집계하는 과정에서

 

서브쿼리와 비슷한 작업을 통해 원하는 값을 불러오는 것을 알려주고 싶었던 문제로 추정되네요!

 

문제를 풀이할수록 1~5 단계 전부 유사한 쿼리가 반복된다는 것이 느껴지시나요?

 

비율을 구할 때 새로운 SELECT 문을 통해 데이터를 값을 불러온 것을 제외하면

 

이전 문제들에서 다 다룬 방법들을 통해 문제를 풀어서 따로 코멘트할 것은 없습니다!

 

혹시나 더 좋은 쿼리나 어렵게 느껴지신 부분이 있다면 댓글로 남겨주세요! 

 

---

설명이 어려운 부분 혹은 잘 이해가 안 가는 부분

그리고 더 궁금한 내용이 있다면

언제든지 댓글로 남겨주세요!

빠르게 궁금증을 해결해 드릴게요!

공감과 댓글은 큰 힘이 됩니다!

오늘도 블로그 방문해 주시고

포스팅 읽어주셔서 감사합니다!