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

문제

MYSQL

내가 작성한 정답

SELECT DATE_FORMAT(SALES_DATE,'%Y') YEAR,
        DATE_FORMAT(SALES_DATE,'%m') MONTH,
        COUNT(DISTINCT U.USER_ID) PURCHASED_USERS,
        ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE JOINED LIKE '2021%'),1) PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE JOINED LIKE '2021%'
GROUP BY DATE_FORMAT(SALES_DATE,'%Y'), DATE_FORMAT(SALES_DATE,'%m')

ORACLE

내가 작성한 오답

SELECT TO_CHAR(O.SALES_DATE,'YYYY') YEAR,
    TO_CHAR(O.SALES_DATE,'MM') MONTH,
    COUNT(DISTINCT O.USER_ID) PURCHASED_USERS,
    ROUND(COUNT(DISTINCT O.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(JOINED,'YYYY')='2021'),1) PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE TO_CHAR(U.JOINED,'YYYY') = '2021'
GROUP BY TO_CHAR(O.SALES_DATE,'MM'),TO_CHAR(O.SALES_DATE,'YYYY')
ORDER BY YEAR ASC, MONTH ASC

내가 작성한 정답

SELECT TO_CHAR(O.SALES_DATE,'YYYY') YEAR,
    EXTRACT(MONTH FROM O.SALES_DATE) MONTH,
    COUNT(DISTINCT O.USER_ID) PURCHASED_USERS,
    ROUND(COUNT(DISTINCT O.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(JOINED,'YYYY')='2021'),1) PUCHASED_RATIO
FROM USER_INFO U
JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE TO_CHAR(U.JOINED,'YYYY') = '2021'
GROUP BY EXTRACT(MONTH FROM O.SALES_DATE),TO_CHAR(O.SALES_DATE,'YYYY')
ORDER BY YEAR ASC, MONTH ASC