SQL 연습문제(중급): 데이터 품질
customers — 고객 (등급, 포인트, 가입채널)
사용 테이블
customers — 고객 (등급, 포인트, 가입채널)
products — 상품 (이름, 가격, 재고, 브랜드)
orders — 주문 (상태, 금액, 일시)
order_items — 주문 상세 (수량, 단가)
payments — 결제 (방법, 금액, 상태)
shipping — 배송 (택배사, 추적번호, 상태)
reviews — 리뷰 (평점, 내용)
returns — 반품/교환 (사유, 상태)
customer_addresses — 배송지 (주소, 기본 여부)
coupons — 쿠폰 (할인율, 유효기간)
coupon_usage — 쿠폰 사용 내역
product_prices — 가격 이력 (변경 사유)
학습 범위
NULL check, duplicate check, range check, referential integrity, date inversion, outlier detection, quality dashboard
1. 고객 테이블에서 birth_date, gender, last_login_at 세 칼럼의 NULL 비율을 계산
고객 테이블에서 birth_date, gender, last_login_at 세 칼럼의 NULL 비율을 계산하세요.
힌트 1: SUM(CASE WHEN 칼럼 IS NULL THEN 1 ELSE 0 END)로 NULL 건수를 구합니다.
정답
SELECT
COUNT(*) AS total,
SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) AS null_birth,
ROUND(100.0 * SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_birth,
SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) AS null_gender,
ROUND(100.0 * SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_gender,
SUM(CASE WHEN last_login_at IS NULL THEN 1 ELSE 0 END) AS null_login,
ROUND(100.0 * SUM(CASE WHEN last_login_at IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_login
FROM customers;
실행 결과 (1행)
| total | null_birth | pct_birth | null_gender | pct_gender | null_login | pct_login |
|---|---|---|---|---|---|---|
| 5230 | 738 | 14.10 | 529 | 10.10 | 281 | 5.40 |
2. 동일한 이메일을 가진 고객이 있는지 확인하세요. 중복 이메일과 해당 건수를 출력하세요.
동일한 이메일을 가진 고객이 있는지 확인하세요. 중복 이메일과 해당 건수를 출력하세요.
힌트 1: GROUP BY email 후 HAVING COUNT(*) > 1로 중복을 탐지합니다.
정답
SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
3. 상품 가격이 비정상적인 행을 찾으세요. 가격이 0 이하이거나, 원가보다 낮은 상품(역마진).
상품 가격이 비정상적인 행을 찾으세요. 가격이 0 이하이거나, 원가보다 낮은 상품(역마진).
힌트 1: WHERE price <= 0 OR price < cost_price로 비정상 가격을 탐지합니다.
정답
SELECT name, sku, price, cost_price, ROUND(price - cost_price, 0) AS margin
FROM products
WHERE price <= 0 OR price < cost_price
ORDER BY margin ASC;
실행 결과 (총 15행 중 상위 7행)
| name | sku | price | cost_price | margin |
|---|---|---|---|---|
| SAPPHIRE NITRO+ RX 7900 XTX 블랙 | GP-AMD-SAP-00088 | 867,300.00 | 1,049,800.00 | -182,500.00 |
| Razer Blade 18 블랙 | LA-GAM-RAZ-00001 | 2,987,500.00 | 3,086,700.00 | -99,200.00 |
| Razer Blade 18 | LA-GAM-RAZ-00180 | 1,806,800.00 | 1,901,300.00 | -94,500.00 |
| 레노버 IdeaPad Flex 5 | LA-2IN-ETC-00062 | 1,550,800.00 | 1,641,300.00 | -90,500.00 |
| LG 그램 14 | LA-GEN-LGE-00226 | 1,734,000.00 | 1,820,500.00 | -86,500.00 |
| MSI Radeon RX 9070 VENTUS 3X 화이트 | GP-AMD-MSI-00006 | 383,100.00 | 431,800.00 | -48,700.00 |
| be quiet! Pure Power 12 M 850W 화이트 | PS-BEQ-00023 | 185,100.00 | 231,500.00 | -46,400.00 |
4. 주문 금액이 0 이하인데 취소 상태가 아닌 주문을 찾으세요.
주문 금액이 0 이하인데 취소 상태가 아닌 주문을 찾으세요.
힌트 1: total_amount <= 0이면서 취소가 아닌 주문은 데이터 오류일 수 있습니다.
정답
SELECT order_number, status, total_amount, ordered_at
FROM orders
WHERE total_amount <= 0
AND status NOT IN ('cancelled', 'returned', 'return_requested')
ORDER BY ordered_at DESC;
5. 리뷰 평점이 허용 범위(1~5)를 벗어나는 행이 있는지 확인하세요.
리뷰 평점이 허용 범위(1~5)를 벗어나는 행이 있는지 확인하세요.
힌트 1: WHERE rating NOT BETWEEN 1 AND 5로 확인합니다.
정답
SELECT id, product_id, customer_id, rating
FROM reviews
WHERE rating < 1 OR rating > 5;
6. 재고 수량이 음수인 상품을 찾으세요.
재고 수량이 음수인 상품을 찾으세요.
힌트 1: WHERE stock_qty < 0으로 확인합니다.
정답
SELECT name, sku, stock_qty, is_active
FROM products
WHERE stock_qty < 0
ORDER BY stock_qty ASC;
7. 같은 주문에 같은 상품이 중복 등록되었는지 확인하세요.
같은 주문에 같은 상품이 중복 등록되었는지 확인하세요.
힌트 1: GROUP BY order_id, product_id 후 HAVING COUNT(*) > 1로 중복 조합을 탐지합니다.
정답
SELECT order_id, product_id, COUNT(*) AS cnt
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
8. 주문이 존재하지 않는 결제 레코드(고아 레코드)가 있는지 확인하세요.
주문이 존재하지 않는 결제 레코드(고아 레코드)가 있는지 확인하세요.
힌트 1: payments LEFT JOIN orders로 연결 후, orders.id IS NULL인 결제를 찾습니다.
정답
SELECT p.id AS payment_id, p.order_id, p.method, p.amount
FROM payments AS p
LEFT JOIN orders AS o ON p.order_id = o.id
WHERE o.id IS NULL;
9. 고객 가입일보다 주문일이 빠른 주문이 있는지 확인하세요. (시간 역전)
고객 가입일보다 주문일이 빠른 주문이 있는지 확인하세요. (시간 역전)
힌트 1: orders JOIN customers로 연결 후, ordered_at < created_at인 행을 찾습니다.
정답
SELECT
o.order_number, o.ordered_at, c.name,
c.created_at AS signup_date,
ROUND(JULIANDAY(c.created_at) - JULIANDAY(o.ordered_at), 1) AS days_diff
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.ordered_at < c.created_at
ORDER BY days_diff DESC;
10. 배송 완료일이 출고일보다 빠른 비정상 레코드를 찾으세요.
배송 완료일이 출고일보다 빠른 비정상 레코드를 찾으세요.
힌트 1: 두 날짜가 모두 NOT NULL인 행 중에서 delivered_at < shipped_at인 경우를 찾습니다.
정답
SELECT
sh.id, o.order_number, sh.shipped_at, sh.delivered_at,
ROUND(JULIANDAY(sh.delivered_at) - JULIANDAY(sh.shipped_at), 1) AS days_diff
FROM shipping AS sh
INNER JOIN orders AS o ON sh.order_id = o.id
WHERE sh.shipped_at IS NOT NULL AND sh.delivered_at IS NOT NULL
AND sh.delivered_at < sh.shipped_at;
11. 배송 완료인데 주문 상태가 배송 완료 이후 단계로 진행되지 않은 불일치를 찾으세요.
배송 완료인데 주문 상태가 배송 완료 이후 단계로 진행되지 않은 불일치를 찾으세요.
힌트 1: 배송이 delivered이면 주문 상태가 delivered, confirmed, return_requested, returned 중 하나여야 합니다.
정답
SELECT
o.order_number, o.status AS order_status,
sh.status AS ship_status, sh.delivered_at
FROM orders AS o
INNER JOIN shipping AS sh ON o.id = sh.order_id
WHERE sh.status = 'delivered'
AND o.status NOT IN ('delivered', 'confirmed', 'return_requested', 'returned');
12. 취소된 주문인데 배송 완료 기록이 있는 모순 데이터를 찾으세요.
취소된 주문인데 배송 완료 기록이 있는 모순 데이터를 찾으세요.
힌트 1: orders.status = 'cancelled'이면서 shipping.status = 'delivered'인 주문은 논리적 모순입니다.
정답
SELECT
o.order_number, o.status AS order_status, o.cancelled_at,
sh.status AS ship_status, sh.delivered_at
FROM orders AS o
INNER JOIN shipping AS sh ON o.id = sh.order_id
WHERE o.status = 'cancelled' AND sh.status = 'delivered';
13. 단종된 상품이 단종 이후에 주문된 적이 있는지 확인하세요. 최근 20건.
단종된 상품이 단종 이후에 주문된 적이 있는지 확인하세요. 최근 20건.
힌트 1: 단종 상품의 discontinued_at과 주문의 ordered_at을 비교합니다.
정답
SELECT
p.name, p.discontinued_at, o.order_number, o.ordered_at
FROM products AS p
INNER JOIN order_items AS oi ON p.id = oi.product_id
INNER JOIN orders AS o ON oi.order_id = o.id
WHERE p.discontinued_at IS NOT NULL AND o.ordered_at > p.discontinued_at
ORDER BY o.ordered_at DESC
LIMIT 20;
14. 상품의 현재 가격과 가격 이력 테이블의 현재 유효 가격이 불일치하는 상품을 찾으세요.
상품의 현재 가격과 가격 이력 테이블의 현재 유효 가격이 불일치하는 상품을 찾으세요.
힌트 1: product_prices에서 ended_at IS NULL인 행이 현재 적용 중인 가격입니다.
정답
SELECT
p.name, p.price AS current_price, pp.price AS history_price,
ROUND(p.price - pp.price, 0) AS diff
FROM products AS p
INNER JOIN product_prices AS pp ON p.id = pp.product_id
WHERE pp.ended_at IS NULL AND p.price <> pp.price
ORDER BY ABS(p.price - pp.price) DESC;
15. 결제 금액과 주문 금액이 일치하지 않는 주문을 찾으세요. 상위 20건.
결제 금액과 주문 금액이 일치하지 않는 주문을 찾으세요. 상위 20건.
힌트 1: 주문별 결제 합계를 서브쿼리로 구한 뒤, orders.total_amount와 비교합니다.
정답
SELECT
o.order_number, o.total_amount AS order_amount,
pay_sum.paid_total,
ROUND(o.total_amount - pay_sum.paid_total, 2) AS diff
FROM orders AS o
INNER JOIN (
SELECT order_id, SUM(amount) AS paid_total
FROM payments WHERE status = 'completed'
GROUP BY order_id
) AS pay_sum ON o.id = pay_sum.order_id
WHERE ABS(o.total_amount - pay_sum.paid_total) > 1
ORDER BY ABS(diff) DESC
LIMIT 20;
16. 고객별 기본 배송지가 2개 이상인 고객을 찾으세요.
고객별 기본 배송지가 2개 이상인 고객을 찾으세요.
힌트 1: customer_addresses에서 is_default = 1인 행을 고객별로 세어, 1보다 큰 고객을 찾습니다.
정답
SELECT c.name, c.email, COUNT(*) AS default_count
FROM customers AS c
INNER JOIN customer_addresses AS ca ON c.id = ca.customer_id
WHERE ca.is_default = 1
GROUP BY c.id, c.name, c.email
HAVING COUNT(*) > 1;
17. 쿠폰 사용 테이블에서 per_user_limit을 초과하여 사용한 건이 있는지 확인하세요.
쿠폰 사용 테이블에서 per_user_limit을 초과하여 사용한 건이 있는지 확인하세요.
힌트 1: coupon_usage를 coupon_id, customer_id로 그룹화하여 사용 횟수를 센 뒤, coupons.per_user_limit과 비교합니다.
정답
SELECT
cp.code AS coupon_code, cp.name AS coupon_name,
c.name AS customer_name,
COUNT(*) AS usage_count, cp.per_user_limit
FROM coupon_usage AS cu
INNER JOIN coupons AS cp ON cu.coupon_id = cp.id
INNER JOIN customers AS c ON cu.customer_id = c.id
GROUP BY cu.coupon_id, cu.customer_id, cp.code, cp.name, c.name, cp.per_user_limit
HAVING COUNT(*) > cp.per_user_limit
ORDER BY usage_count DESC;
18. 반품 테이블에서 환불 금액 합계가 원래 주문 금액을 초과하는 주문을 찾으세요.
반품 테이블에서 환불 금액 합계가 원래 주문 금액을 초과하는 주문을 찾으세요.
힌트 1: 주문별 환불 금액 합계를 구한 뒤 orders.total_amount와 비교합니다.
정답
SELECT
o.order_number, o.total_amount AS order_amount,
SUM(ret.refund_amount) AS total_refund,
ROUND(SUM(ret.refund_amount) - o.total_amount, 2) AS over_refund
FROM returns AS ret
INNER JOIN orders AS o ON ret.order_id = o.id
GROUP BY ret.order_id, o.order_number, o.total_amount
HAVING SUM(ret.refund_amount) > o.total_amount
ORDER BY over_refund DESC;
실행 결과 (총 69행 중 상위 7행)
| order_number | order_amount | total_refund | over_refund |
|---|---|---|---|
| ORD-20211201-14549 | 1,339,931.00 | 1,344,800.00 | 4,869.00 |
| ORD-20250612-33985 | 1,145,546.00 | 1,150,400.00 | 4,854.00 |
| ORD-20191218-04896 | 1,485,673.00 | 1,490,500.00 | 4,827.00 |
| ORD-20160925-00299 | 1,124,799.00 | 1,129,400.00 | 4,601.00 |
| ORD-20251102-36300 | 253,362.00 | 257,900.00 | 4,538.00 |
| ORD-20241019-29928 | 2,368,896.00 | 2,373,400.00 | 4,504.00 |
| ORD-20200101-05044 | 126,002.00 | 130,500.00 | 4,498.00 |
19. 데이터 완전성 점수를 customers, products, orders 3개 테이블에 대해 계산하세요. UN
데이터 완전성 점수를 customers, products, orders 3개 테이블에 대해 계산하세요. UNION ALL로 합쳐 보고서를 만드세요.
힌트 1: 각 테이블에서 NULL 가능 칼럼의 non-NULL 비율을 계산하고, 여러 칼럼의 평균을 구합니다.
정답
SELECT '고객' AS table_name, COUNT(*) AS total_rows,
ROUND(100.0 * (
(1.0 - 1.0 * SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN gender IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN last_login_at IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN acquisition_channel IS NULL THEN 1 ELSE 0 END) / COUNT(*))
) / 4.0, 1) AS completeness_pct
FROM customers
UNION ALL
SELECT '상품', COUNT(*),
ROUND(100.0 * (
(1.0 - 1.0 * SUM(CASE WHEN description IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN specs IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN weight_grams IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN model_number IS NULL THEN 1 ELSE 0 END) / COUNT(*))
) / 4.0, 1)
FROM products
UNION ALL
SELECT '주문', COUNT(*),
ROUND(100.0 * (
(1.0 - 1.0 * SUM(CASE WHEN notes IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN completed_at IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN cancelled_at IS NULL THEN 1 ELSE 0 END) / COUNT(*))
+ (1.0 - 1.0 * SUM(CASE WHEN staff_id IS NULL THEN 1 ELSE 0 END) / COUNT(*))
) / 4.0, 1)
FROM orders;
실행 결과 (3행)
| table_name | total_rows | completeness_pct |
|---|---|---|
| 고객 | 5230 | 92.60 |
| 상품 | 281 | 83.60 |
| 주문 | 37,557 | 32.90 |
20. 한 쿼리로 주요 데이터 품질 지표를 요약하세요. 가입일 역전, 고아 결제, 배송일 역전, 중복 주문상세, 음
한 쿼리로 주요 데이터 품질 지표를 요약하세요. 가입일 역전, 고아 결제, 배송일 역전, 중복 주문상세, 음수 재고, 취소+배송완료 모순 건수를 한 행에 출력.
힌트 1: 각 품질 점검을 (SELECT COUNT(*) FROM ... WHERE 조건) 스칼라 서브쿼리로 만들어 하나의 SELECT에 나열합니다.
정답
SELECT
(SELECT COUNT(*) FROM orders AS o INNER JOIN customers AS c ON o.customer_id = c.id WHERE o.ordered_at < c.created_at) AS orders_before_signup,
(SELECT COUNT(*) FROM payments AS p LEFT JOIN orders AS o ON p.order_id = o.id WHERE o.id IS NULL) AS orphan_payments,
(SELECT COUNT(*) FROM shipping WHERE shipped_at IS NOT NULL AND delivered_at IS NOT NULL AND delivered_at < shipped_at) AS date_inversions,
(SELECT COUNT(*) FROM (SELECT order_id, product_id FROM order_items GROUP BY order_id, product_id HAVING COUNT(*) > 1)) AS duplicate_items,
(SELECT COUNT(*) FROM products WHERE stock_qty < 0) AS negative_stock,
(SELECT COUNT(*) FROM orders AS o INNER JOIN shipping AS sh ON o.id = sh.order_id WHERE o.status = 'cancelled' AND sh.status = 'delivered') AS cancelled_but_delivered;
실행 결과 (1행)
| orders_before_signup | orphan_payments | date_inversions | duplicate_items | negative_stock | cancelled_but_delivered |
|---|---|---|---|---|---|
| 0 | 0 | 0 | 0 | 0 | 0 |