Data Quality Checks

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;

SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

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;

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;

SELECT id, product_id, customer_id, rating
FROM reviews
WHERE rating < 1 OR rating > 5;

SELECT name, sku, stock_qty, is_active
FROM products
WHERE stock_qty < 0
ORDER BY stock_qty ASC;

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

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;

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;

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;

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');

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';

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;

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;

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;

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;

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;

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;

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;

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;