상품을 구매한 회원 비율 구하기-LV5

- 문제

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

 

코딩테스트 연습 - 상품을 구매한 회원 비율 구하기 | 프로그래머스 스쿨

프로그래머스

 

- 키포인트

2021년 가입한 회원자와, 2021년 가입한 회원 중 상품을 구매한 회원자를 잘 구별하기

 

- 코드

WITH JOIN_2021 AS (
SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED)=2021)

SELECT YEAR(O.SALES_DATE) AS YEAR , MONTH(O.SALES_DATE) AS MONTH,
COUNT(DISTINCT(O.USER_ID)) AS PURCHASED_USERS, 
ROUND(COUNT(DISTINCT(O.USER_ID))/COUNT(DISTINCT(J.USER_ID)),1) AS PUCHASED_RATIO
FROM ONLINE_SALE O, JOIN_2021 J
WHERE O.USER_ID IN (
SELECT USER_ID
FROM JOIN_2021)
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;

우선 2021년 가입한 회원자를 CTE로 JOIN_2021 이름으로 생성함

WHERE절 :

이후 JOIN_2021에 있는 ID만을 가지는 ONLINE_SALE의 구매 회원 필터링 > 2021년 가입한 회원 중 상품을 구매한 사람

DISTINCT(O.USER_ID) : 2021년 가입한 회원 중 상품을 구매한 사람

DISTINCT(J.USER_ID) : 2021년 가입한 회원

SELECT year(sales_date) as year,month(sales_date) as month, count(distinct user_id) as puchased_users,
round(count(distinct user_id)/(select count(*) from user_info where year(joined)=2021),1) as puchased_ratioㅎ
from online_sale
where user_id in (select user_id from user_info where year(joined) = 2021)
group by year(sales_date),month(sales_date)
order by year,month