-- Real-time performance metrics
SELECT
    operation_type,
    COUNT(*) as occurrence,
    AVG(duration_ms) as avg_duration,
    MAX(duration_ms) as max_duration,
    SUM(rows_affected) as total_rows
FROM performance_log
WHERE timestamp >= NOW() - INTERVAL 1 HOUR
GROUP BY operation_type
ORDER BY avg_duration DESC

-- Query pattern analysis
WITH query_stats AS (
    SELECT
        query_pattern,
        COUNT(*) as execution_count,
        AVG(execution_time) as avg_time,
        SUM(rows_examined) as total_rows
    FROM query_log
    GROUP BY query_pattern
)
SELECT
    query_pattern,
    execution_count,
    avg_time,
    ROUND(avg_time * execution_count, 2) as total_impact
FROM query_stats
ORDER BY total_impact DESC

-- Resource usage tracking
SELECT
    instance_name,
    metric_name,
    AVG(value) as avg_value,
    MAX(value) as peak_value,
    MIN(value) as min_value
FROM system_metrics
WHERE collect_time >= NOW() - INTERVAL 24 HOUR
GROUP BY instance_name,

-- Customer behavior analysis
WITH customer_metrics AS (
    SELECT
        c.customer_segment,
        COUNT(DISTINCT o.order_id) as orders,
        SUM(o.total_amount) as revenue,
        AVG(o.items_count) as avg_items
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    WHERE o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
    GROUP BY c.customer_segment
)
SELECT
    customer_segment,
    orders,
    revenue,
    avg_items,
    revenue / orders as avg_order_value
FROM customer_metrics
ORDER BY revenue DESC

-- Transaction analysis
SELECT
    DATE_FORMAT(transaction_time, '%Y-%m-%d %H:00:00') as hour,
    transaction_type,
    COUNT(*) as transaction_count,
    SUM(amount) as total_amount,
    AVG(processing_time) as avg_processing_time
FROM transactions
WHERE transaction_time >= NOW() - INTERVAL 24 HOUR
GROUP BY hour, transaction_type
ORDER BY hour DESC

-- Key performance indicators
SELECT
    metric_name,
    current_value,
    threshold_value,
    CASE
        WHEN current_value > threshold_value THEN 'Alert'
        WHEN current_value > threshold_value * 0.8 THEN 'Warning'
        ELSE 'Normal'
    END as status
FROM performance_metrics
WHERE collect_time >= NOW() - INTERVAL 5 MINUTE

-- System health check
SELECT
    component_name,
    status,
    last_check_time,
    error_count,
    warning_count
FROM system_health
WHERE last_check_time >= NOW() - INTERVAL 1 HOUR
ORDER BY error_count DESC, warning_count DESC

-- Index usage statistics
SELECT
    table_name,
    index_name,
    usage_count,
    last_used,
    size_mb,
    CASE
        WHEN usage_count = 0 THEN 'Unused'
        WHEN usage_count < 100 THEN 'Low Usage'
        ELSE 'Active'
    END as status
FROM index_statistics
ORDER BY usage_count DESC

-- Query optimization suggestions
SELECT
    query_hash,
    execution_count,
    avg_cpu_time,
    avg_logical_reads,
    suggested_indexes,
    improvement_impact
FROM query_optimizer_recommendations
WHERE analysis_date = CURRENT_DATE
ORDER BY improvement_impact DESC