-- Data Exploration for Trendy Fashion E-commerce Analytics
-- This script performs initial data discovery and quality assessment
-- Execute these queries in order to understand the dataset

-- =====================================================================
-- 1. DATA OVERVIEW AND BASIC STATISTICS
-- =====================================================================

-- Customer overview
SELECT 
    'Customers' as table_name,
    COUNT(*) as total_records,
    COUNT(DISTINCT customer_id) as unique_customers,
    MIN(registration_date) as earliest_registration,
    MAX(registration_date) as latest_registration
FROM customers;

-- Product overview  
SELECT 
    'Products' as table_name,
    COUNT(*) as total_records,
    COUNT(DISTINCT product_id) as unique_products,
    COUNT(DISTINCT category) as unique_categories,
    COUNT(DISTINCT brand) as unique_brands,
    ROUND(AVG(price), 2) as avg_price,
    ROUND(MIN(price), 2) as min_price,
    ROUND(MAX(price), 2) as max_price
FROM products;

-- Orders overview
SELECT 
    'Orders' as table_name,
    COUNT(*) as total_records,
    COUNT(DISTINCT order_id) as unique_orders,
    COUNT(DISTINCT customer_id) as unique_customers_with_orders,
    MIN(order_date) as earliest_order,
    MAX(order_date) as latest_order,
    ROUND(SUM(total_amount), 2) as total_revenue,
    ROUND(AVG(total_amount), 2) as avg_order_value
FROM orders
WHERE status = 'completed';

-- =====================================================================
-- 2. DATA QUALITY ASSESSMENT
-- =====================================================================

-- Check for missing values in customers table
SELECT 
    'customer_id' as field_name,
    COUNT(*) as total_records,
    COUNT(customer_id) as non_null_records,
    COUNT(*) - COUNT(customer_id) as null_records,
    ROUND(COUNT(customer_id) * 100.0 / COUNT(*), 2) as completeness_pct
FROM customers

UNION ALL

SELECT 
    'email' as field_name,
    COUNT(*) as total_records,
    COUNT(email) as non_null_records,
    COUNT(*) - COUNT(email) as null_records,
    ROUND(COUNT(email) * 100.0 / COUNT(*), 2) as completeness_pct
FROM customers

UNION ALL

SELECT 
    'registration_date' as field_name,
    COUNT(*) as total_records,
    COUNT(registration_date) as non_null_records,
    COUNT(*) - COUNT(registration_date) as null_records,
    ROUND(COUNT(registration_date) * 100.0 / COUNT(*), 2) as completeness_pct
FROM customers;

-- Check for duplicate customers
SELECT 
    email,
    COUNT(*) as duplicate_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

-- Check order data quality
SELECT 
    status,
    COUNT(*) as order_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders), 2) as percentage,
    ROUND(AVG(total_amount), 2) as avg_amount,
    ROUND(SUM(total_amount), 2) as total_revenue
FROM orders
GROUP BY status
ORDER BY order_count DESC;

-- =====================================================================
-- 3. BUSINESS INSIGHTS AND PATTERNS
-- =====================================================================

-- Customer registration patterns by month
SELECT 
    DATE_TRUNC('month', registration_date::date) as registration_month,
    COUNT(*) as new_customers
FROM customers
WHERE registration_date >= '2022-01-01'
GROUP BY DATE_TRUNC('month', registration_date::date)
ORDER BY registration_month;

-- Top product categories by revenue
SELECT 
    p.category,
    COUNT(DISTINCT o.order_id) as total_orders,
    ROUND(SUM(o.total_amount), 2) as total_revenue,
    ROUND(AVG(o.total_amount), 2) as avg_order_value,
    ROUND(SUM(o.total_amount) * 100.0 / (
        SELECT SUM(total_amount) FROM orders WHERE status = 'completed'
    ), 2) as revenue_percentage
FROM orders o
JOIN products p ON 1=1  -- This is a simplified join for demonstration
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;

-- Customer geographic distribution
SELECT 
    state,
    COUNT(*) as customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 2) as percentage
FROM customers
GROUP BY state
ORDER BY customer_count DESC
LIMIT 10;

-- Monthly sales trends
SELECT 
    DATE_TRUNC('month', order_date::date) as order_month,
    COUNT(*) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    ROUND(SUM(total_amount), 2) as total_revenue,
    ROUND(AVG(total_amount), 2) as avg_order_value
FROM orders
WHERE status = 'completed'
  AND order_date >= '2022-01-01'
GROUP BY DATE_TRUNC('month', order_date::date)
ORDER BY order_month;

-- =====================================================================
-- 4. CUSTOMER BEHAVIOR ANALYSIS
-- =====================================================================

-- Customer order frequency distribution
WITH customer_order_counts AS (
    SELECT 
        customer_id,
        COUNT(*) as total_orders,
        ROUND(SUM(total_amount), 2) as total_spent,
        MIN(order_date) as first_order_date,
        MAX(order_date) as last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT 
    CASE 
        WHEN total_orders = 1 THEN '1 order'
        WHEN total_orders BETWEEN 2 AND 3 THEN '2-3 orders'
        WHEN total_orders BETWEEN 4 AND 6 THEN '4-6 orders'
        WHEN total_orders BETWEEN 7 AND 10 THEN '7-10 orders'
        ELSE '11+ orders'
    END as order_frequency_bucket,
    COUNT(*) as customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customer_order_counts), 2) as percentage,
    ROUND(AVG(total_spent), 2) as avg_total_spent
FROM customer_order_counts
GROUP BY 
    CASE 
        WHEN total_orders = 1 THEN '1 order'
        WHEN total_orders BETWEEN 2 AND 3 THEN '2-3 orders'
        WHEN total_orders BETWEEN 4 AND 6 THEN '4-6 orders'
        WHEN total_orders BETWEEN 7 AND 10 THEN '7-10 orders'
        ELSE '11+ orders'
    END
ORDER BY 
    CASE 
        WHEN order_frequency_bucket = '1 order' THEN 1
        WHEN order_frequency_bucket = '2-3 orders' THEN 2
        WHEN order_frequency_bucket = '4-6 orders' THEN 3
        WHEN order_frequency_bucket = '7-10 orders' THEN 4
        ELSE 5
    END;

-- Customer spending distribution
WITH customer_spending AS (
    SELECT 
        customer_id,
        ROUND(SUM(total_amount), 2) as total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT 
    CASE 
        WHEN total_spent < 50 THEN 'Under $50'
        WHEN total_spent BETWEEN 50 AND 149 THEN '$50-$149'
        WHEN total_spent BETWEEN 150 AND 299 THEN '$150-$299'
        WHEN total_spent BETWEEN 300 AND 499 THEN '$300-$499'
        ELSE '$500+'
    END as spending_bucket,
    COUNT(*) as customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customer_spending), 2) as percentage,
    ROUND(AVG(total_spent), 2) as avg_spent_in_bucket
FROM customer_spending
GROUP BY 
    CASE 
        WHEN total_spent < 50 THEN 'Under $50'
        WHEN total_spent BETWEEN 50 AND 149 THEN '$50-$149'
        WHEN total_spent BETWEEN 150 AND 299 THEN '$150-$299'
        WHEN total_spent BETWEEN 300 AND 499 THEN '$300-$499'
        ELSE '$500+'
    END
ORDER BY 
    CASE 
        WHEN spending_bucket = 'Under $50' THEN 1
        WHEN spending_bucket = '$50-$149' THEN 2
        WHEN spending_bucket = '$150-$299' THEN 3
        WHEN spending_bucket = '$300-$499' THEN 4
        ELSE 5
    END;

-- =====================================================================
-- 5. SEGMENTATION PREPARATION DATA
-- =====================================================================

-- RFM Analysis preparation: Calculate key metrics for each customer
WITH rfm_metrics AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        c.registration_date,
        c.state,
        -- Recency: Days since last order
        COALESCE(
            EXTRACT(DAYS FROM (CURRENT_DATE - MAX(o.order_date::date))), 
            999
        ) as days_since_last_order,
        
        -- Frequency: Number of completed orders
        COUNT(CASE WHEN o.status = 'completed' THEN 1 END) as total_orders,
        
        -- Monetary: Total amount spent on completed orders
        COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total_amount END), 0) as total_spent,
        
        -- Additional metrics
        MIN(o.order_date) as first_order_date,
        MAX(o.order_date) as last_order_date,
        ROUND(AVG(CASE WHEN o.status = 'completed' THEN o.total_amount END), 2) as avg_order_value
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name, c.email, c.registration_date, c.state
)
SELECT 
    *,
    -- Create RFM score buckets (1-5 scale)
    CASE 
        WHEN days_since_last_order <= 30 THEN 5
        WHEN days_since_last_order <= 60 THEN 4
        WHEN days_since_last_order <= 90 THEN 3
        WHEN days_since_last_order <= 180 THEN 2
        ELSE 1
    END as recency_score,
    
    CASE 
        WHEN total_orders >= 10 THEN 5
        WHEN total_orders >= 6 THEN 4
        WHEN total_orders >= 4 THEN 3
        WHEN total_orders >= 2 THEN 2
        WHEN total_orders >= 1 THEN 1
        ELSE 0
    END as frequency_score,
    
    CASE 
        WHEN total_spent >= 500 THEN 5
        WHEN total_spent >= 300 THEN 4
        WHEN total_spent >= 150 THEN 3
        WHEN total_spent >= 50 THEN 2
        WHEN total_spent > 0 THEN 1
        ELSE 0
    END as monetary_score
FROM rfm_metrics
ORDER BY total_spent DESC
LIMIT 100;  -- Show top 100 customers for review

-- =====================================================================
-- 6. DATA QUALITY ISSUES AND RECOMMENDATIONS
-- =====================================================================

-- Check for customers without orders (potential data quality issue)
SELECT 
    'Customers without orders' as issue_type,
    COUNT(*) as count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 2) as percentage
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- Check for orders with unusual amounts (potential data quality issues)
SELECT 
    'Unusually high orders (>$1000)' as issue_type,
    COUNT(*) as count
FROM orders
WHERE total_amount > 1000 AND status = 'completed'

UNION ALL

SELECT 
    'Unusually low orders (<$10)' as issue_type,
    COUNT(*) as count
FROM orders
WHERE total_amount < 10 AND status = 'completed';

-- Orders placed before customer registration (data integrity issue)
SELECT 
    'Orders before registration' as issue_type,
    COUNT(*) as count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date < c.registration_date;

-- =====================================================================
-- 7. SUMMARY STATISTICS FOR DASHBOARD
-- =====================================================================

-- Key metrics summary for dashboard
SELECT 
    'Key Business Metrics' as metric_category,
    (SELECT COUNT(*) FROM customers) as total_customers,
    (SELECT COUNT(DISTINCT customer_id) FROM orders WHERE status = 'completed') as customers_with_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') as total_orders,
    (SELECT ROUND(SUM(total_amount), 2) FROM orders WHERE status = 'completed') as total_revenue,
    (SELECT ROUND(AVG(total_amount), 2) FROM orders WHERE status = 'completed') as avg_order_value,
    (SELECT COUNT(*) FROM products) as total_products,
    (SELECT COUNT(DISTINCT category) FROM products) as product_categories;

-- Marketing consent statistics
SELECT 
    marketing_consent,
    COUNT(*) as customer_count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM customers), 2) as percentage
FROM customers
GROUP BY marketing_consent
ORDER BY marketing_consent DESC;

PRINT 'Data exploration complete!';
PRINT 'Next steps:';
PRINT '1. Review the results above for data quality issues';
PRINT '2. Run the customer segmentation analysis';  
PRINT '3. Create the dashboard using these insights';
PRINT '4. Set up monitoring for ongoing data quality';