SQLD 기출문제 예상문제 370번

다음 문제를 풀어보세요.

위 SQL 쿼리가 고객의 세 번째 요구사항인 '완료된 모든 주문을 대상으로 한 평균 상품 수량 (단, 상품 수량이 명시되지 않은 주문은 0으로 간주하여 평균 계산에 포함)'을 정확히 반영하지 못하는 가장 큰 이유와 이를 해결하기 위한 올바른 SQL 구문을 고르시오.

[테이블 구조: CUSTOMER_ORDER]
CREATE TABLE CUSTOMER_ORDER (
    order_id VARCHAR2(10) PRIMARY KEY,
    customer_id VARCHAR2(10),
    order_date DATE NOT NULL,
    order_status VARCHAR2(20) NOT NULL,
    order_amount NUMBER(10, 2), -- 주문 금액 (PENDING 상태 시 NULL 가능)
    shipping_date DATE,          -- 배송일 (PENDING 상태 시 NULL 가능)
    item_quantity NUMBER(5)      -- 상품 수량 (서비스 주문 또는 수량 미정 시 NULL 가능)
);

[샘플 데이터]
INSERT INTO CUSTOMER_ORDER VALUES ('ORD001', 'CUST001', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 'COMPLETED', 150.00, TO_DATE('2023-01-17', 'YYYY-MM-DD'), 2);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD002', 'CUST002', TO_DATE('2023-01-16', 'YYYY-MM-DD'), 'PENDING', NULL, NULL, 1);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD003', 'CUST001', TO_DATE('2023-01-18', 'YYYY-MM-DD'), 'COMPLETED', 200.00, TO_DATE('2023-01-20', 'YYYY-MM-DD'), NULL); -- 서비스 주문
INSERT INTO CUSTOMER_ORDER VALUES ('ORD004', 'CUST003', TO_DATE('2023-01-19', 'YYYY-MM-DD'), 'SHIPPED', 75.50, TO_DATE('2023-01-19', 'YYYY-MM-DD'), 3);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD005', 'CUST002', TO_DATE('2023-01-20', 'YYYY-MM-DD'), 'PENDING', NULL, NULL, 5);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD006', 'CUST001', TO_DATE('2023-01-21', 'YYYY-MM-DD'), 'COMPLETED', 300.00, TO_DATE('2023-01-22', 'YYYY-MM-DD'), 4);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD007', 'CUST004', TO_DATE('2023-01-22', 'YYYY-MM-DD'), 'CANCELLED', 50.00, NULL, 1);
INSERT INTO CUSTOMER_ORDER VALUES ('ORD008', 'CUST004', TO_DATE('2023-01-23', 'YYYY-MM-DD'), 'PENDING', NULL, NULL, NULL);

[고객 요구사항]
고객사는 COMPLETED 상태의 모든 주문에 대해 다음과 같은 통계를 요청했습니다:

총 완료된 주문 건수
완료된 주문들의 총 합계 금액
완료된 모든 주문을 대상으로 한 평균 상품 수량 (단, 상품 수량이 명시되지 않은 주문은 0으로 간주하여 평균 계산에 포함)

[초기 SQL 쿼리]
SELECT
    COUNT(order_id) AS total_completed_orders,
    SUM(order_amount) AS total_completed_amount,
    AVG(item_quantity) AS avg_item_quantity_per_order
FROM
    CUSTOMER_ORDER
WHERE
    order_status = 'COMPLETED';

문제: COUNT(order_id)는 order_id가 NULL인 경우를 포함하지 않아 실제 완료된 주문 건수보다 적게 셀 수 있습니다. 해결: COUNT(*)로 변경해야 합니다.

문제: SUM(order_amount)는 order_amount가 NULL인 주문을 합산에서 제외합니다. 고객은 NULL을 0으로 간주한 총합을 원했습니다. 해결: SUM(COALESCE(order_amount, 0))으로 변경해야 합니다.

문제: AVG(item_quantity)는 item_quantity가 NULL인 주문을 평균 계산에서 제외합니다. 고객은 상품 수량이 없는 주문도 0으로 간주하여 평균에 포함하기를 원했습니다. 해결: AVG(COALESCE(item_quantity, 0))으로 변경해야 합니다.

문제: WHERE order_status = 'COMPLETED' 조건만으로는 shipping_date가 NULL인 COMPLETED 주문이 포함될 수 있습니다. 배송된 주문만을 대상으로 해야 합니다. 해결: WHERE order_status = 'COMPLETED' AND shipping_date IS NOT NULL로 변경해야 합니다.