오프라인•온라인 판매 데이터 통합하기

게시 2024/02/02

By tonyleekorea 1 분읽는 시간

https://school.programmers.co.kr/learn/courses/30/lessons/131537

문제 풀이

  
SELECT TO_CHAR(SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE, PRODUCT_ID, NVL(USER_ID, NULL) AS "USER_ID", SALES_AMOUNT 
FROM (
     SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
     FROM ONLINE_SALE
     WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
     UNION
     SELECT SALES_DATE, PRODUCT_ID, NULL AS "USER_ID", SALES_AMOUNT
     FROM OFFLINE_SALE 
     WHERE TO_CHAR(SALES_DATE, 'YYYY-MM') = '2022-03'
     );

  
SELECT
    TO_CHAR(A.SALES_DATE, 'YYYY-MM-DD') AS SALES_DATE,
    A.PRODUCT_ID,
    A.USER_ID,
    A.SALES_AMOUNT
FROM (
    SELECT
        USER_ID,
        PRODUCT_ID,
        SALES_AMOUNT,
        SALES_DATE
    FROM
        ONLINE_SALE
    WHERE
        TO_CHAR(SALES_DATE, 'MM') = '03'

    UNION

    SELECT
        NULL AS USER_ID,
        PRODUCT_ID,
        SALES_AMOUNT,
        SALES_DATE
    FROM
        OFFLINE_SALE
    WHERE
        TO_CHAR(SALES_DATE, 'MM') = '03'
) A
ORDER BY
    SALES_DATE, A.PRODUCT_ID, A.USER_ID;