-- 1. Customer segments where average exceeds median
WITH segment_stats AS (
    SELECT 
        customer_segment,
        AVG(order_value) as avg_value,
        COUNT(*) as order_count
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY customer_segment
),
median_calc AS (
    SELECT 
        customer_segment,
        order_value,
        ROW_NUMBER() OVER (PARTITION BY customer_segment ORDER BY order_value) as rn,
        COUNT(*) OVER (PARTITION BY customer_segment) as cnt
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
)
SELECT 
    s.customer_segment,
    s.avg_value,
    m.median_value,
    s.order_count
FROM segment_stats s
JOIN (
    SELECT 
        customer_segment,
        AVG(order_value) as median_value
    FROM median_calc
    WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
    GROUP BY customer_segment
) m ON s.customer_segment = m.customer_segment
WHERE s.avg_value > m.median_value * 1.2;

-- 2. Products with abnormal return rates
SELECT 
    product_id,
    product_name,
    COUNT(*) as total_sales,
    SUM(CASE WHEN returned = TRUE THEN 1 ELSE 0 END) as returns,
    AVG(CASE WHEN returned = TRUE THEN 1 ELSE 0 END) as return_rate,
    STD(CASE WHEN returned = TRUE THEN 1 ELSE 0 END) as return_std
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY product_id
HAVING 
    COUNT(*) >= 20  -- Minimum sample size
    AND AVG(CASE WHEN returned = TRUE THEN 1 ELSE 0 END) > (
        SELECT AVG(return_rate) + 2 * STD(return_rate)
        FROM (
            SELECT AVG(CASE WHEN returned = TRUE THEN 1 ELSE 0 END) as return_rate
            FROM order_items
            GROUP BY product_id
        ) as rates
    );

-- 3. Months exceeding both previous and year-ago
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') as month,
        SUM(amount) as revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) as prev_month,
    LAG(revenue, 12) OVER (ORDER BY month) as year_ago
FROM monthly_sales
HAVING 
    revenue > LAG(revenue, 1) OVER (ORDER BY month) * 1.05
    AND revenue > LAG(revenue, 12) OVER (ORDER BY month) * 1.10;

-- 4. High price variance but stable sales
SELECT 
    category,
    COUNT(DISTINCT product_id) as products,
    STD(price) as price_std,
    AVG(price) as price_avg,
    STD(monthly_sales) as sales_std,
    AVG(monthly_sales) as sales_avg,
    STD(price) / AVG(price) as price_cv,
    STD(monthly_sales) / AVG(monthly_sales) as sales_cv
FROM (
    SELECT 
        p.category,
        p.product_id,
        p.price,
        DATE_FORMAT(o.order_date, '%Y-%m') as month,
        SUM(oi.quantity) as monthly_sales
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    GROUP BY p.category, p.product_id, month
) as product_sales
GROUP BY category
HAVING 
    STD(price) / AVG(price) > 0.5  -- High price variance
    AND STD(monthly_sales) / AVG(monthly_sales) < 0.2;  -- Low sales variance

-- 5. Suppliers with consistent rating improvement
WITH supplier_ratings AS (
    SELECT 
        s.supplier_id,
        s.supplier_name,
        QUARTER(r.review_date) as quarter,
        YEAR(r.review_date) as year,
        AVG(r.rating) as avg_rating,
        ROW_NUMBER() OVER (PARTITION BY s.supplier_id ORDER BY YEAR(r.review_date), QUARTER(r.review_date)) as quarter_num
    FROM suppliers s
    JOIN products p ON s.supplier_id = p.supplier_id
    JOIN reviews r ON p.product_id = r.product_id
    GROUP BY s.supplier_id, year, quarter
)
SELECT 
    supplier_id,
    supplier_name,
    COUNT(*) as quarters_tracked,
    MIN(avg_rating) as min_rating,
    MAX(avg_rating) as max_rating
FROM supplier_ratings
GROUP BY supplier_id
HAVING 
    COUNT(*) >= 4  -- At least 4 quarters
    AND MIN(CASE WHEN quarter_num > 1 THEN avg_rating END) > 
        MIN(CASE WHEN quarter_num = 1 THEN avg_rating END);

-- 6. Customers with irregular patterns
SELECT 
    customer_id,
    COUNT(*) as order_count,
    AVG(DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))) as avg_days_between,
    STD(DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))) as std_days_between,
    MIN(amount) as min_order,
    MAX(amount) as max_order,
    STD(amount) as std_amount
FROM orders
GROUP BY customer_id
HAVING 
    COUNT(*) >= 10
    AND STD(amount) > AVG(amount) * 0.8  -- High amount variance
    AND STD(DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))) > 
        AVG(DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))) * 0.5;

-- 7. Product combinations (association rules)
SELECT 
    oi1.product_id as product_a,
    oi2.product_id as product_b,
    COUNT(DISTINCT oi1.order_id) as orders_together,
    COUNT(DISTINCT oi1.order_id) * 100.0 / (
        SELECT COUNT(DISTINCT order_id)
        FROM order_items
        WHERE product_id = oi1.product_id
    ) as support_percentage
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id
    AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
HAVING 
    COUNT(DISTINCT oi1.order_id) >= 10
    AND COUNT(DISTINCT oi1.order_id) * 100.0 / (
        SELECT COUNT(DISTINCT order_id)
        FROM order_items
        WHERE product_id = oi1.product_id
    ) > 50;

-- 8. Seasonal categories using CV
SELECT 
    category,
    COUNT(DISTINCT MONTH(order_date)) as months_active,
    AVG(monthly_revenue) as avg_monthly_revenue,
    STD(monthly_revenue) as std_monthly_revenue,
    STD(monthly_revenue) / AVG(monthly_revenue) as coefficient_of_variation,
    CASE 
        WHEN STD(monthly_revenue) / AVG(monthly_revenue) > 0.5 THEN 'Highly Seasonal'
        WHEN STD(monthly_revenue) / AVG(monthly_revenue) > 0.3 THEN 'Moderately Seasonal'
        ELSE 'Stable'
    END as seasonality
FROM (
    SELECT 
        p.category,
        MONTH(o.order_date) as month,
        SUM(oi.quantity * oi.unit_price) as monthly_revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY p.category, MONTH(o.order_date)
) as monthly_data
GROUP BY category
HAVING STD(monthly_revenue) / AVG(monthly_revenue) > 0.3;