3 Commits

Author	SHA1	Message	Date
Alphaeus Mote	730553bffd	Fix merchant categories duplicate key and fraud_types query column names ... MERCHANT CATEGORIES FIX: - Fixed duplicate category_code '7995' (Gambling vs Online Gambling) - Changed Online Gambling code from '7995' to '7996' - Fixed duplicate 'Direct Marketing' names to be unique - Changed to 'Direct Marketing Inbound' and 'Direct Marketing Outbound' - This was causing UNIQUE constraint violation preventing merchant_categories from loading FRAUD TYPES QUERY FIX: - Fixed column names in verification query - Changed from fraud_type_code to fraud_code - Changed from fraud_type_name to fraud_name - Matches actual table schema in 01-create-tables.sql IMPACT: - Merchant categories will now load correctly (33 categories) - Fraud types verification query will work without errors - Setup script will complete all 5 verification queries successfully	2025-10-24 12:25:43 -04:00
Alphaeus Mote	aa803bd3bd	Expand to Business Analytics: Add Customer, Sales, and KPI models ... Major expansion from fraud detection to comprehensive business analytics: DATABASE CHANGES: - Renamed database from 'fraud_detection' to 'business_analytics' - Renamed user from 'fraud_analyst' to 'data_analyst' - Expanded from 20 to 39 tables across 4 business models NEW MODELS (19 tables): 1. Customer Analytics (5 tables): - customer_segments, customer_lifetime_value, churn_predictions - customer_satisfaction, engagement_metrics 2. Sales & Revenue Analytics (6 tables): - product_catalog, sales_transactions, sales_targets - sales_performance, revenue_forecasts 3. KPI & Metrics (8 tables): - kpi_definitions, daily_metrics, monthly_summaries - trend_analysis, dashboard_snapshots - report_definitions, report_executions, data_quality_checks DATA GENERATION: - Extended generate_data.sh with 6 new steps (now 15 total) - Added CLV calculations for all customers - Added churn predictions based on transaction recency - Added 30K customer satisfaction surveys - Added 1M sales transactions linked to 24 products - Added 90 days of daily KPI metrics - Added 24 months of business summaries SQL EXERCISES (3 new levels): - Level 2: Customer Analytics (10 exercises + 3 challenges) - Level 3: Sales & Revenue Analysis (12 exercises + 3 challenges) - Level 4: KPI Dashboards & Metrics (12 exercises + 3 challenges) DOCUMENTATION: - Updated README.md with business analytics focus - Updated QUICKSTART.md with new data generation steps - Updated SETUP_COMPLETE.md with 39-table architecture - Added DATA_MODELS.md with complete model specifications - Added WHATS_NEW.md with migration guide SEED DATA: - Added 8 customer segments (VIP, High Value, etc.) - Added 24 products across 5 categories - Added 16 KPI definitions across 4 categories - Added 8 standard report definitions All changes maintain idempotency and backward compatibility with existing fraud detection functionality.	2025-10-23 14:17:12 -04:00
Alphaeus Mote	b30733ccad	Add complete financial fraud detection database with Docker, schema, data generation, and SQL exercises ... - Docker setup with PostgreSQL 16 and DB-UI web interface - Comprehensive 20+ table schema with fraud detection patterns - Idempotent shell scripts for data generation (no Python dependency) - Realistic geographic data (100 US cities, 210 world cities) - 5M+ transactions with embedded fraud patterns (velocity, geographic, structuring, etc.) - Progressive SQL exercises from beginner to advanced fraud detection - Complete documentation and quick start guide - Setup and verification scripts	2025-10-23 13:53:30 -04:00