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

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

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

첫번째 시도

# 2021 전체 가입자수 158명
, joined AS ( SELECT user_id
                    , joined
                    , COUNT(*) OVER() AS CNT
                FROM USER_INFO
                WHERE joined like '%2021%')
                
# 2021년에 가입했고, 구매를 한사람들의 비율

SELECT YEAR
    , MONTH
    , PUCHASED_USERS
    , ROUND(PUCHASED_USERS/CNT,1) AS PUCHASED_RATIO
FROM (SELECT YEAR
        , MONTH
        , COUNT(DISTINCT USER_ID)AS PUCHASED_USERS
        , CNT
    FROM (SELECT a.user_id
            ,  b.sales_amount
            ,  a.cnt
            ,  DATE_FORMAT(b.sales_date, '%Y') AS YEAR
            ,  DATE_FORMAT(b.sales_date, '%m') AS MONTH
          FROM  joined a
            LEFT JOIN  online_sale b ON a.user_id = b.user_id ) A 
    WHERE YEAR IS NOT NULL
    GROUP BY YEAR,MONTH  ) B
ORDER BY YEAR, MONTH

회고 🤦‍♂️

생각의 흐름대로 써내려가봤다. 처음에 윈도우 함수를 이용하는 문제인가 했는데 생각보다 잘 이용되지 못했다.. 그래서 결국 서브쿼리로 한단계씩 쌓아올리는 전략을 짰는데 조금은 비효율적인 것 같다. 좀 더 연습하고 다시 풀어봐야지

두번째 시도

서브쿼리들을 조금 줄이고, join 하지 않고 구할 수 있을까 고민해보았습니다.

with info as (
      SELECT user_id
      FROM USER_INFO
      WHERE YEAR(joined) ='2021'
      )


# ONLINE_SALE 테이블에는 구매이력이 담겨져 있습니다. 문제를 풀기 위해 가장 먼저 접근 한 것은
# 서브쿼리로 만들어 놓은 info 테이블을 이용하여 , 2021년에 가입한 user_info 들만 가져옵니다. (WHERE)
# 문제에서 원하는 TABLE 결과에 맞게 구입한 날짜의 YEAR, MONTH를 추출해주고
# YEAR 과 MONTH를 기준으로 GROUP BY 해줍니다.
# 그리고 DISTINCT 한 USER_ID를 COUNT 해주면 = '2021년에 가입한 회원중 상품을 구매한 회원수' 가 됩니다. 

WITH USER_21 AS( 
  SELECT USER_ID #, COUNT(USER_ID) AS TOTAL_USER
  FROM USER_INFO 
  WHERE YEAR(JOINED) ='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(USER_ID) FROM USER_21),1) AS PUCHASED_RATIO

FROM ONLINE_SALE O
WHERE USER_ID  IN(SELECT USER_ID FROM USER_21) 
# INNER JOIN USER_21 U USING(USER_ID)를 이용할 수 있지만 
# 테이블을 합치는 과정, 비용적으로 보아도 WHERE이 더 효율적이다!

GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH