-- 1. Average order value per customer segment
SELECT 
    CASE 
        WHEN total_orders < 3 THEN 'New'
        WHEN total_orders < 10 THEN 'Regular'
        ELSE 'VIP'
    END AS customer_segment,
    COUNT(*) AS customer_count,
    AVG(avg_order_value) AS segment_avg_order_value,
    MIN(avg_order_value) AS min_avg_order,
    MAX(avg_order_value) AS max_avg_order
FROM (
    SELECT 
        customer_id,
        COUNT(*) AS total_orders,
        AVG(amount) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) customer_stats
GROUP BY customer_segment
ORDER BY segment_avg_order_value DESC;

-- 2. Top 3 products per category
WITH product_rankings AS (
    SELECT 
        p.category_id,
        c.category_name,
        p.product_id,
        p.product_name,
        SUM(oi.quantity) AS units_sold,
        SUM(oi.quantity * oi.unit_price) AS revenue,
        ROW_NUMBER() OVER (
            PARTITION BY p.category_id 
            ORDER BY SUM(oi.quantity * oi.unit_price) DESC
        ) AS rank_in_category
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    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_id, p.product_id
)
SELECT *
FROM product_rankings
WHERE rank_in_category <= 3
ORDER BY category_id, rank_in_category;

-- 3. Month-over-month growth
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) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) / 
        LAG(revenue) OVER (ORDER BY month) * 100, 
        2
    ) AS growth_percentage
FROM monthly_sales
ORDER BY month;

-- 4. Customers above average
WITH overall_avg AS (
    SELECT AVG(amount) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
)
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    AVG(o.amount) AS customer_avg_order,
    (SELECT avg_order_value FROM overall_avg) AS overall_average,
    AVG(o.amount) - (SELECT avg_order_value FROM overall_avg) AS difference
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.customer_id
HAVING AVG(o.amount) > (SELECT avg_order_value FROM overall_avg)
ORDER BY customer_avg_order DESC;

-- 5. Comprehensive summary report
SELECT 
    'Overall Statistics' AS metric,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    MIN(amount) AS min_order,
    MAX(amount) AS max_order,
    STD(amount) AS std_deviation
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

UNION ALL

SELECT 
    CONCAT('Status: ', status) AS metric,
    COUNT(DISTINCT customer_id),
    COUNT(*),
    SUM(amount),
    AVG(amount),
    MIN(amount),
    MAX(amount),
    STD(amount)
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY status;

-- 6. Running totals and moving averages
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7_days,
    COUNT(*) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS orders_in_window
FROM orders
WHERE customer_id = 123
ORDER BY order_date;

-- 7. Median calculation
SELECT 
    category_id,
    AVG(price) AS mean_price,
    (
        SELECT AVG(price)
        FROM (
            SELECT price
            FROM products p2
            WHERE p2.category_id = p1.category_id
            ORDER BY price
            LIMIT 2 - (SELECT COUNT(*) FROM products p3 WHERE p3.category_id = p1.category_id) % 2
            OFFSET (SELECT (COUNT(*) - 1) / 2 FROM products p4 WHERE p4.category_id = p1.category_id)
        ) AS median_calc
    ) AS median_price
FROM products p1
GROUP BY category_id;

-- 8. Customer lifetime value
SELECT 
    c.customer_id,
    c.customer_name,
    DATE(c.created_at) AS acquisition_date,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(o.amount) AS lifetime_revenue,
    AVG(o.amount) AS avg_order_value,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) AS customer_lifespan_days,
    SUM(o.amount) / GREATEST(DATEDIFF(CURDATE(), c.created_at), 1) AS daily_value,
    SUM(o.amount) / GREATEST(DATEDIFF(CURDATE(), c.created_at) / 30, 1) AS monthly_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id 
    AND o.status = 'completed'
GROUP BY c.customer_id
ORDER BY lifetime_revenue DESC;