PS/SQL

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

hwajae 2023. 5. 5. 18:35
반응형

문제

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블입니다. USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.

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

 


개념 공부하기

GROUP BY, COUNT GROUP 그룹화 후 COUNT 세기
JOIN, INNER JOIN JOIN : 두 테이블 합치기

 

풀이

문제를 읽으면서 조건들을 분리하며 이해해 본다.

2021년 가입한 정체 회원, 상품 구매 이력이 있는 회원,

상품 구매 회원의 비율 ( 2021 가입, 구매 이력 회원 / 2021가입, 전체 회원)

그리고 이것들을 년, 월 별로 출력을 해야 하니 그룹을 묶어줘야겠다.

  1. 우선 user table과 online table을 user_id로 join을 시킨 뒤 where절로 2021년 가입한 회원들만 추려준다.
  2. 문제에서 원하는 년도와 월은 DATE_FORMAT()을 이용하여 추출한 뒤 column명을 year, month로 지정하여 나중에 group by (column명)을 깔끔하게 해 준다.
  3. 년, 월별로 그룹을 묶어준 뒤 해당 월에 구입한 회원 총 수를 count()를 이용해 구해준다.
  4. (주의) join 된 테이블 결과는 한 회원이 여러 번 구매 이력이 있을 수 있기 때문에 distinct를 이용하여 중복 회원 id를 제거해 준 뒤 count를 세줘야 한다.
  5. 상품 구매 회원 비율을 구하기 위해 2021년에 가입한 총 회원 수를 구해줘야 된다.
  6. (SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%') 서브 쿼리를 이용해 간단하게 구해준다.
  7. 문제에서 둘째 자리에서 반올림을 해줘야 하기 때문에 round() 이용해 준다.
  8. 문제에서 원하는 대로 order by

Round() 함수에서 ex) round(0.1234, 2)는 소수점 둘째 자리까지 반올림을 하는 의미이다. 문제에서는 소수점 둘째 자리에서 반올림을 해야 하기 때문에, 첫째 자리까지 반올림한다는 의미로 이해할 수 있다.

 

SELECT DATE_FORMAT(O.SALES_DATE, '%Y') AS YEAR,
        DATE_FORMAT(O.SALES_DATE, '%m') AS MONTH,
        COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE joined LIKE '2021%'), 1) AS PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O
ON U.USER_ID = O.USER_ID
WHERE U.JOINED LIKE '2021%'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH
;

 


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

 

프로그래머스

코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.

programmers.co.kr

 

반응형