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

 

문제

 

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

프로그래머스

 

문제풀이

 

SELECT YEAR
             , TO_NUMBER(MONTH)
             , COUNT(1) AS PURCHASED_USERS
             , TO_NUMBER(ROUND(COUNT(1) / (SELECT COUNT(1) FROM USER_INFO WHERE                  TO_CHAR(JOINED,'YYYY') = 2021),1)) AS PUCHASED_RATIO
  FROM ( SELECT TO_CHAR(OS.SALES_DATE,'YYYY') AS YEAR
                             , TO_CHAR(OS.SALES_DATE,'MM') AS MONTH
                             , OS.USER_ID
                   FROM ONLINE_SALE OS
                     JOIN USER_INFO UI ON OS.USER_ID = UI.USER_ID
                WHERE TO_CHAR(UI.JOINED,'YYYY') = 2021
                GROUP BY TO_CHAR(OS.SALES_DATE,'YYYY'), TO_CHAR(OS.SALES_DATE,'MM'), OS.USER_ID
      )
 GROUP BY YEAR, MONTH
 ORDER BY YEAR, MONTH