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

https://9566.tistory.com/471

[SQL 프로그래머스 JOIN] 상품을 구매한 회원 비율 구하기 (ORCALE, MYSQL)

정답

SELECT TO_CHAR(SALES_DATE,'YYYY') YEAR
        ,TO_NUMBER(TO_CHAR(SALES_DATE,'MM')) MONTH
        ,COUNT(DISTINCT(USER_ID)) PURCHASED_USERS
        ,ROUND(COUNT(DISTINCT(USER_ID))/(
            SELECT COUNT(USER_ID)
            FROM USER_INFO
            WHERE TO_CHAR(JOINED,'YYYY') ='2021'
        ),1) PUCHASED_RATIO
FROM ONLINE_SALE 
WHERE USER_ID IN (
    SELECT USER_ID
    FROM USER_INFO 
    WHERE TO_CHAR(JOINED,'YYYY') = '2021'
)
GROUP BY TO_CHAR(SALES_DATE,'YYYY'),TO_CHAR(SALES_DATE,'MM')
ORDER BY 1,2

풀이

USER_INFO 테이블에서 2021년에 가입한 전체 회원 USER_ID

SELECT USER_ID
FROM USER_INFO 
WHERE TO_CHAR(JOINED,'YYYY') = '2021'

SELECT *
FROM ONLINE_SALE 
WHERE USER_ID IN (
    SELECT USER_ID
    FROM USER_INFO 
    WHERE TO_CHAR(JOINED,'YYYY') = '2021'
)

• USER_ID 중복된것이 있기 때문에 DISTINCT 사용해야 된다.

SELECT TO_CHAR(SALES_DATE,'YYYY')
    ,TO_CHAR(SALES_DATE,'MM')
FROM ONLINE_SALE 
WHERE USER_ID IN (
    SELECT USER_ID
    FROM USER_INFO 
    WHERE TO_CHAR(JOINED,'YYYY') = '2021'
)
GROUP BY TO_CHAR(SALES_DATE,'YYYY'),TO_CHAR(SALES_DATE,'MM')

SELECT TO_CHAR(SALES_DATE,'YYYY') YEAR
        ,TO_NUMBER(TO_CHAR(SALES_DATE,'MM')) MONTH
        ,COUNT(DISTINCT(USER_ID)) PURCHASED_USERS
        ,ROUND(COUNT(DISTINCT(USER_ID))/(
            SELECT COUNT(USER_ID)
            FROM USER_INFO
            WHERE TO_CHAR(JOINED,'YYYY') ='2021'
        ),1) PUCHASED_RATIO
FROM ONLINE_SALE 
WHERE USER_ID IN (
    SELECT USER_ID
    FROM USER_INFO 
    WHERE TO_CHAR(JOINED,'YYYY') = '2021'
)
GROUP BY TO_CHAR(SALES_DATE,'YYYY'),TO_CHAR(SALES_DATE,'MM')