[SQL문제풀기] 상품을 구매한 회원 비율 구하기

문제

MYSQL

내가 작성한 정답

SELECT YEAR(S.SALES_DATE) YEAR, MONTH(S.SALES_DATE) MONTH,
        COUNT(DISTINCT S.USER_ID) PURCHASED_USERS,
        ROUND(COUNT(DISTINCT S.USER_ID)/(SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = '2021'),1) PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID AND YEAR(U.JOINED) = '2021'
WHERE S.USER_ID IS NOT NULL
GROUP BY MONTH(S.SALES_DATE)
ORDER BY 2;

ORACLE

내가 작성한 정답

SELECT TO_CHAR(S.SALES_DATE,'YYYY')+0 YEAR, TO_CHAR(S.SALES_DATE,'MM')+0 MONTH,
        COUNT(DISTINCT S.USER_ID) PURCHASED_USERS, 
        ROUND(COUNT(DISTINCT S.USER_ID)/(SELECT COUNT(USER_ID) FROM USER_INFO WHERE TO_CHAR(JOINED,'YY') = '21'),1) PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE S ON U.USER_ID = S.USER_ID AND TO_CHAR(U.JOINED,'YY') = '21' 
WHERE S.USER_ID IS NOT NULL
GROUP BY TO_CHAR(S.SALES_DATE,'YYYY'), TO_CHAR(S.SALES_DATE,'MM')
ORDER BY 2;