Data Completeness — Finding Missing Data

-- Comprehensive NULL report for a table
SELECT
  COUNT(*) AS total_rows,
  COUNT(*) - COUNT(full_name) AS missing_name,
  COUNT(*) - COUNT(email) AS missing_email,
  COUNT(*) - COUNT(phone) AS missing_phone,
  COUNT(*) - COUNT(address) AS missing_address,
  COUNT(*) - COUNT(date_of_birth) AS missing_dob
FROM users;

-- NULL percentage per column (tells you how bad it is)
SELECT
  ROUND(100.0 * (COUNT(*) - COUNT(full_name)) / COUNT(*), 2)
    AS name_null_pct,
  ROUND(100.0 * (COUNT(*) - COUNT(email)) / COUNT(*), 2)
    AS email_null_pct,
  ROUND(100.0 * (COUNT(*) - COUNT(phone)) / COUNT(*), 2)
    AS phone_null_pct
FROM users;
-- If email_null_pct > 0% and email is required, that is a bug

-- Basic count validation after migration
SELECT 'source' AS system, COUNT(*) AS record_count
FROM source_users
UNION ALL
SELECT 'target', COUNT(*)
FROM users;
-- Both counts should match

-- Check for gaps in expected sequences
-- Every day should have at least one transaction
SELECT d.date AS missing_date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 day'::interval
) d(date)
LEFT JOIN transactions t
  ON DATE(t.created_at) = d.date
WHERE t.transaction_id IS NULL
  AND d.date <= CURRENT_DATE;
-- Any missing dates could indicate a system outage or data loss

-- Every user who placed an order should have a shipping address
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN addresses a ON o.user_id = a.user_id
WHERE a.address_id IS NULL
  AND o.status = 'shipped';

-- Every active product should have at least one image
SELECT p.product_id, p.name
FROM products p
LEFT JOIN product_images pi ON p.product_id = pi.product_id
WHERE pi.image_id IS NULL
  AND p.is_active = true;

-- Every employee should have an assigned department
SELECT employee_id, full_name
FROM employees
WHERE department_id IS NULL;

-- Every completed order should have a payment record
SELECT o.order_id, o.user_id, o.total_amount
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL
  AND o.status = 'completed';