[SQL] 프로그래머스 [Level-4] 년, 월, 성별 별 상품 구매 회원 수 구하기

<MySQL>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT YEAR(A.SALES_DATE) AS YEAR
              , MONTH(A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE), B.USER_ID
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

<Oracle>

SELECT MST.YEAR
     , MST.MONTH
     , MST.GENDER
     , COUNT(MST.GENDER) AS USERS
  FROM (
         SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR
              , EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH
              , B.GENDER
           FROM ONLINE_SALE A
              , USER_INFO B
          WHERE A.USER_ID = B.USER_ID
            AND B.GENDER IN (0, 1)
       GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), EXTRACT(MONTH FROM A.SALES_DATE), B.USER_ID, B.GENDER
       ) MST
GROUP BY MST.YEAR, MST.MONTH, MST.GENDER
ORDER BY MST.YEAR, MST.MONTH, MST.GENDER

MySQL에서 MONTH(날짜), DATE_FORMAT(날짜, '%c') 차이

Oracle에서 EXTRACT(MONTH FROM 날짜), TO_CHAR(날짜, 'FMMM') 차이

정렬 기준의 차이를 확인할 수 있다.

프로그래머스 년, 월, 성별 별 상품 구매 회원 수 구하기 SQL