USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
SELECT TO_CHAR(B.SALES_DATE , 'YYYY') AS YEAR
, TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM')) AS MONTH
, A.GENDER AS GENDER
, COUNT(DISTINCT(A.USER_ID)) AS USERS
FROM ONLINE_SALE B
JOIN USER_INFO A ON B.USER_ID = A.USER_ID
GROUP BY TO_CHAR(B.SALES_DATE , 'YYYY')
, TO_NUMBER(TO_CHAR(B.SALES_DATE , 'MM'))
, A.GENDER
HAVING A.GENDER IS NOT NULL
ORDER BY YEAR ASC , MONTH ASC , A.GENDER ASC
SELECT TO_CHAR(B.SALES_DATE, 'YYYY') AS YEAR
, TO_NUMBER(TO_CHAR(B.SALES_DATE, 'MM')) AS MONTH
, A.GENDER AS GENDER
, COUNT(DISTINCT(A.USER_ID)) AS USERS
FROM USER_INFO A
, ONLINE_SALE B
WHERE A.USER_ID = B.USER_ID
GROUP BY TO_CHAR(B.SALES_DATE, 'YYYY')
, TO_NUMBER(TO_CHAR(B.SALES_DATE, 'MM'))
, A.GENDER
HAVING A.GENDER IS NOT NULL
ORDER BY YEAR ASC, MONTH ASC, GENDER ASC