[프로그래머스 ORACLE SQL]상품을 구매한 회원 비율 구하기★INNER JOIN★DISTINCT

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

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

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

프로그래머스

1. USER_INFO 테이블 ==> USER_ID , JOINED(년, 월)

SELECT USER_ID , JOINED ,TO_CHAR(JOINED, 'YYYY') AS YEAR, TO_CHAR(JOINED , 'MM') AS MONTH FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = 2021

2. 2021년 가입한 회원 수 ==> 중복 제거하기

SELECT  COUNT(DISTINCT(USER_ID)) AS COUNT_2021 FROM USER_INFO
GROUP BY TO_CHAR(JOINED ,'YYYY')  
HAVING TO_CHAR(JOINED ,'YYYY') = 2021

3. 상품을 구매한 회원(자료가 2022년 만 있다)

SELECT DISTINCT(USER_ID) , TO_CHAR(SALES_DATE , 'YYYY') AS YEAR , TO_CHAR(SALES_DATE , 'MM') AS MONTH 
FROM ONLINE_SALE

4. 2021년 가입한 회원 USER_ID 와 2021년에 가입한 회원 중 상품을 구매한 회원 JOIN

SELECT A.USER_ID  , B.YEAR , B.MONTH  FROM 
(SELECT USER_ID , JOINED ,TO_CHAR(JOINED, 'YYYY') AS YEAR, TO_CHAR(JOINED , 'MM') AS MONTH FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = 2021) A INNER JOIN 

(SELECT DISTINCT(USER_ID) , TO_CHAR(SALES_DATE , 'YYYY') AS YEAR , TO_CHAR(SALES_DATE , 'MM') AS MONTH FROM ONLINE_SALE) B

ON A.USER_ID = B.USER_ID

==> 2번이 A , 3번이 B ==> INNER JOIN'

 

 

5. 최종

SELECT YEAR, TO_NUMBER(MONTH), COUNT(*) AS PURCHASED_USERS , ROUND(COUNT(*)/(SELECT  COUNT(DISTINCT(USER_ID)) AS COUNT_2021 FROM USER_INFO
GROUP BY TO_CHAR(JOINED ,'YYYY')  
HAVING TO_CHAR(JOINED ,'YYYY') = 2021),1) AS PURCHASED_RATIO FROM (
SELECT A.USER_ID  , B.YEAR , B.MONTH  FROM 
(SELECT USER_ID , JOINED ,TO_CHAR(JOINED, 'YYYY') AS YEAR, TO_CHAR(JOINED , 'MM') AS MONTH FROM USER_INFO
WHERE TO_CHAR(JOINED, 'YYYY') = 2021) A INNER JOIN 

(SELECT DISTINCT(USER_ID) , TO_CHAR(SALES_DATE , 'YYYY') AS YEAR , TO_CHAR(SALES_DATE , 'MM') AS MONTH FROM ONLINE_SALE) B

ON A.USER_ID = B.USER_ID) 

GROUP BY YEAR, MONTH

ORDER BY YEAR ASC , MONTH ASC

결과