데이터 품질 점검

SELECT
    o.order_number, o.ordered_at,
    c.name, c.created_at AS signup_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.ordered_at < c.created_at;

SELECT p.id, p.order_id, p.amount
FROM payments p
LEFT JOIN orders o ON p.order_id = o.id
WHERE o.id IS NULL;

-- customers 테이블의 NULL 비율
SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) AS null_birthdate,
    ROUND(100.0 * SUM(CASE WHEN birth_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 1) AS pct_birthdate,
    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;

SELECT
    o.order_number, o.status AS order_status,
    sh.status AS shipping_status, sh.delivered_at
FROM orders o
INNER JOIN shipping sh ON o.id = sh.order_id
WHERE sh.status = 'delivered'
  AND o.status NOT IN ('delivered', 'confirmed', 'return_requested', 'returned');

SELECT
    sh.id, o.order_number,
    sh.shipped_at, sh.delivered_at,
    ROUND(JULIANDAY(sh.delivered_at) - JULIANDAY(sh.shipped_at), 1) AS days
FROM shipping sh
INNER JOIN orders 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;

SELECT order_id, product_id, COUNT(*) AS cnt
FROM order_items
GROUP BY order_id, product_id
HAVING COUNT(*) > 1;

-- 주문 금액이 평균의 10배 이상인 이상치
SELECT order_number, total_amount, ordered_at
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) * 10 FROM orders)
ORDER BY total_amount DESC;

SELECT
    o.order_number, o.status, o.cancelled_at,
    sh.status AS ship_status, sh.delivered_at
FROM orders o
INNER JOIN shipping sh ON o.id = sh.order_id
WHERE o.status = 'cancelled'
  AND sh.status = 'delivered';

SELECT
    p.name, p.is_active, p.discontinued_at,
    MAX(o.ordered_at) AS last_ordered
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE p.is_active = 0 OR p.discontinued_at IS NOT NULL
GROUP BY p.id, p.name, p.is_active, p.discontinued_at
HAVING MAX(o.ordered_at) > COALESCE(p.discontinued_at, '9999-12-31');

-- 주문은 있는데 결제가 없는 경우
SELECT o.id, o.order_number
FROM orders o
LEFT JOIN payments p ON o.id = p.order_id
WHERE p.id IS NULL;

SELECT
    p.name, p.price AS current_price,
    pp.price AS latest_history_price
FROM products p
INNER JOIN product_prices pp ON p.id = pp.product_id
WHERE pp.ended_at IS NULL
  AND p.price <> pp.price;

SELECT
    (SELECT COUNT(*) FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE o.ordered_at < c.created_at) AS orders_before_signup,
    (SELECT COUNT(*) FROM payments p LEFT JOIN orders o ON p.order_id = o.id WHERE o.id IS NULL) AS orphan_payments,
    (SELECT COUNT(*) FROM shipping WHERE delivered_at < shipped_at) AS invalid_delivery_dates,
    (SELECT COUNT(*) 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 WHERE total_amount <= 0 AND status NOT IN ('cancelled')) AS zero_amount_orders;