SELECT 
  account_id,
  opening_balance + 
  SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) as current_balance
 FROM accounts a
 LEFT JOIN transactions t ON a.id = t.account_id
 WHERE t.status = 'completed'
 GROUP BY account_id, opening_balance;

SELECT 
  DATE(transaction_date) as date,
  branch_id,
  SUM(CASE WHEN type = 'deposit' THEN amount ELSE 0 END) as total_deposits,
  SUM(CASE WHEN type = 'withdrawal' THEN amount ELSE 0 END) as total_withdrawals,
  COUNT(*) as transaction_count
 FROM transactions
 WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
 GROUP BY DATE(transaction_date), branch_id
 ORDER BY date DESC, branch_id;

WITH account_stats AS (
  SELECT 
  account_id,
  AVG(amount) as avg_amount,
  STDDEV(amount) as std_amount,
  COUNT(*) as tx_count
  FROM transactions
  WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
  GROUP BY account_id
 )
 SELECT 
  t.account_id,
  t.transaction_id,
  t.amount,
  t.transaction_date,
  s.avg_amount,
  (t.amount - s.avg_amount) / NULLIF(s.std_amount, 0) as z_score
 FROM transactions t
 INNER JOIN account_stats s ON t.account_id = s.account_id
 WHERE ABS((t.amount - s.avg_amount) / NULLIF(s.std_amount, 0)) > 3
  AND t.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
 ORDER BY z_score DESC;

SELECT 
  risk_category,
  status,
  COUNT(*) as loan_count,
  SUM(principal_amount) as total_principal,
  SUM(outstanding_balance) as total_outstanding,
  ROUND(AVG(interest_rate), 2) as avg_rate,
  SUM(CASE WHEN days_past_due > 30 THEN outstanding_balance ELSE 0 END) as past_due_30
 FROM loans
 GROUP BY risk_category, status
 ORDER BY risk_category, status;