상품을 구매한 회원 비율 구하기 (Oracle) - programmers

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

테이블은 한번씩만 쓰기

SELECT c.year ,c.month
       ,count(c.user_id) PUCHASED_USERS
       ,round(count(user_id)/max(c.joined_users),1) PUCHASED_RATIO 
       -- 그냥 joined_users만 쓰면 group by 룰 때문에 사용하지 못해 max로 감싸줌
from (
      select a.user_id, year, month, joined_users
         from (
             select user_id, 
                    count(*) over(partition by to_char(joined, 'yyyy')) joined_users
                    -- 2021년 전체 인원수 가져오려고 사용
             from USER_INFO 
             where to_char(joined, 'yyyy') = '2021' 
         ) a, 
         (
            select user_id, 
                   EXTRACT(YEAR from sales_date) year, 
                   EXTRACT(month from sales_date) month 
                   -- to_char말고 처음 써봄.. 결과가 같은데 안되길래 찾아보니 이거써야함..
             from ONLINE_SALE
             group by user_id, EXTRACT(YEAR from sales_date), EXTRACT(month from sales_date)
         ) b
         where a.user_id = b.user_id
     ) c
group by c.year ,c.month
order by 1 asc, 2 asc