Data Exploration

`
-- Data exploration workflow

-- 1. Structure inspection
SELECT column_name, data_type, null_percent
FROM table_schema
WHERE table_name = 'customer_transactions';

-- 2. Distribution analysis
SELECT 
  MIN(transaction_amount) as min_amount,
  MAX(transaction_amount) as max_amount,
  AVG(transaction_amount) as mean_amount,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY transaction_amount) as median,
  STDDEV(transaction_amount) as stddev,
  COUNT(*) as total_transactions,
  COUNT(CASE WHEN transaction_amount IS NULL THEN 1 END) as null_count
FROM customer_transactions;

-- 3. Categorical distribution
SELECT 
  product_category,
  COUNT(*) as count,
  COUNT()  100.0 / SUM(COUNT(*)) OVER () as percentage
FROM transactions
GROUP BY product_category
ORDER BY count DESC;

-- 4. Outlier detection
SELECT 
  *,
  (transaction_amount - avg_amount) / stddev_amount as zscore
FROM (
  SELECT 
    *,
    AVG(transaction_amount) OVER () as avg_amount,
    STDDEV(transaction_amount) OVER () as stddev_amount
  FROM customer_transactions
)
WHERE ABS((transaction_amount - avg_amount) / stddev_amount) > 3;

-- 5. Missing data analysis
SELECT 
  COUNT(CASE WHEN customer_id IS NULL THEN 1 END) as missing_customers,
  COUNT(CASE WHEN product_id IS NULL THEN 1 END) as missing_products,
  COUNT(CASE WHEN order_date IS NULL THEN 1 END) as missing_dates
FROM transactions;
`