DATE 추출은 EXTRACT

프로그래머스 문제 풀이

 

EXTRACT(MONTH FROM SALES_DATE) AS MONTH

프로그래머스

LTRIM으로 MONTH의 0을 제거  (01 > 1, 02 > 2 ...) >>> 결과는 같은데 정답 틀림..

SELECT YEAR, MONTH, GENDER, COUNT(USER_ID) AS USERS
FROM (
	SELECT DISTINCT
    TO_CHAR(A.SALES_DATE, 'YYYY') AS YEAR
    , LTRIM(TO_CHAR(A.SALES_DATE, 'MM'), '0') AS MONTH
    , A.USER_ID
    , B.GENDER    
    FROM ONLINE_SALE A, USER_INFO B
    WHERE A.USER_ID = B.USER_ID
    AND B.GENDER IS NOT NULL        
    )
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;

 

MONTH 추출하는 부분 EXTRACT 변경 >>> 패스

리턴 타입을 NUMBER로 해야 통과되는 거였음! TO_NUMBER로 하니 통과됨

, TO_NUMBER(LTRIM(TO_CHAR(A.SALES_DATE, 'MM'), '0')) AS MONTH

SELECT YEAR, MONTH, GENDER, COUNT(USER_ID) AS USERS
FROM (
	SELECT DISTINCT
    TO_CHAR(A.SALES_DATE, 'YYYY') AS YEAR
    , EXTRACT(MONTH FROM SALES_DATE) AS MONTH
    , A.USER_ID
    , B.GENDER    
    FROM ONLINE_SALE A, USER_INFO B
    WHERE A.USER_ID = B.USER_ID
    AND B.GENDER IS NOT NULL        
    )
GROUP BY YEAR, MONTH, GENDER
ORDER BY YEAR, MONTH, GENDER;