-- Sales trend analysis with year-over-year comparison
WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(sale_date, '%Y-%m') as month,
        SUM(amount) as revenue,
        COUNT(*) as transaction_count
    FROM sales
    WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR)
    GROUP BY month
)
SELECT
    month,
    revenue,
    transaction_count,
    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 month_over_month_growth,
    LAG(revenue, 12) OVER (ORDER BY month) as prev_year_revenue,
    ROUND(
        ((revenue - LAG(revenue, 12) OVER (ORDER BY month)) /
        LAG(revenue, 12) OVER (ORDER BY month) * 100),
        2
    ) as year_over_year_growth
FROM monthly_sales
ORDER BY month DESC

-- Customer retention by signup cohort
WITH cohort_data AS (
    SELECT
        DATE_FORMAT(first_purchase_date, '%Y-%m') as cohort_month,
        customer_id,
        TIMESTAMPDIFF(MONTH,
            first_purchase_date,
            subsequent_purchase_date
        ) as month_number
    FROM (
        SELECT
            customer_id,
            MIN(purchase_date) as first_purchase_date,
            purchase_date as subsequent_purchase_date
        FROM purchases
        GROUP BY customer_id, purchase_date
    ) purchase_dates
),
cohort_sizes AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT customer_id) as cohort_size
    FROM cohort_data
    GROUP BY cohort_month
)
SELECT
    cd.cohort_month,
    cs.cohort_size,
    cd.month_number,
    COUNT(DISTINCT cd.customer_id) as active_customers,
    ROUND(
        COUNT(DISTINCT cd.customer_id) / cs.cohort_size * 100,
        2
    ) as retention_rate
FROM cohort_data cd
JOIN cohort_sizes cs ON cd.cohort_month = cs.cohort_month
GROUP BY cd.cohort_month, cs.cohort_size, cd.month_number
ORDER BY cd.cohort_month,

-- RFM (Recency, Frequency, Monetary) Analysis
WITH customer_metrics AS (
    SELECT
        customer_id,
        DATEDIFF(CURRENT_DATE, MAX(purchase_date)) as recency,
        COUNT(*) as frequency,
        AVG(amount) as avg_purchase,
        SUM(amount) as total_spent
    FROM purchases
    WHERE purchase_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR)
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        NTILE(5) OVER (ORDER BY recency DESC) as r_score,
        NTILE(5) OVER (ORDER BY frequency) as f_score,
        NTILE(5) OVER (ORDER BY total_spent) as m_score
    FROM customer_metrics
)
SELECT
    CASE
        WHEN (r_score + f_score + m_score) >= 13 THEN 'Champions'
        WHEN (r_score + f_score + m_score) >= 10 THEN 'Loyal'
        WHEN (r_score + f_score + m_score) >= 7 THEN 'Regular'
        WHEN (r_score + f_score + m_score) >= 4 THEN 'At Risk'
        ELSE 'Lost'
    END as customer_segment,
    COUNT(*) as customer_count,
    ROUND(AVG(cm.total_spent), 2) as avg_customer_value
FROM customer_segments cs
JOIN customer_metrics cm USING (customer_id)
GROUP BY customer_segment
ORDER BY avg_customer_value DESC

-- Product performance and associations
WITH product_metrics AS (
    SELECT
        p.product_id,
        p.product_name,
        p.category,
        COUNT(DISTINCT s.sale_id) as sale_count,
        SUM(s.quantity) as units_sold,
        SUM(s.quantity * s.price) as revenue,
        COUNT(DISTINCT s.customer_id) as unique_customers
    FROM products p
    JOIN sales s ON p.product_id = s.product_id
    WHERE s.sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH)
    GROUP BY p.product_id, p.product_name, p.category
)
SELECT
    product_name,
    category,
    sale_count,
    units_sold,
    revenue,
    unique_customers,
    ROUND(revenue / units_sold, 2) as avg_unit_price,
    ROUND(units_sold / sale_count, 2) as avg_quantity_per_sale
FROM product_metrics
ORDER BY revenue DESC

-- Sales forecast using moving averages
WITH daily_sales AS (
    SELECT
        sale_date,
        SUM(amount) as daily_revenue
    FROM sales
    WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
    GROUP BY sale_date
),
moving_averages AS (
    SELECT
        sale_date,
        daily_revenue,
        AVG(daily_revenue) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
        ) as week_moving_avg,
        AVG(daily_revenue) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
        ) as month_moving_avg
    FROM daily_sales
)
SELECT
    sale_date,
    daily_revenue,
    week_moving_avg,
    month_moving_avg,
    ROUND(
        (daily_revenue - week_moving_avg) / week_moving_avg * 100,
        2
    ) as weekly_variance_pct
FROM moving_averages
ORDER BY sale_date DESC

-- Identifying purchase patterns
WITH purchase_patterns AS (
    SELECT
        customer_id,
        EXTRACT(HOUR FROM purchase_time) as hour_of_day,
        DAYNAME(purchase_date) as day_of_week,
        COUNT(*) as purchase_count,
        AVG(amount) as avg_purchase_amount
    FROM purchases
    WHERE purchase_date >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
    GROUP BY
        customer_id,
        EXTRACT(HOUR FROM purchase_time),
        DAYNAME(purchase_date)
)
SELECT
    hour_of_day,
    day_of_week,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(purchase_count) as total_purchases,
    ROUND(AVG(avg_purchase_amount), 2) as avg_transaction_value
FROM purchase_patterns
GROUP BY hour_of_day, day_of_week
ORDER BY total_purchases DESC

-- Time series data for charts
SELECT
    DATE_FORMAT(date, '%Y-%m-%d') as date,
    metric_name,
    value
FROM metrics
WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
AND metric_name IN ('revenue', 'users', 'conversion_rate')
ORDER BY date,

-- Key performance indicators
SELECT
    metric_name,
    current_value,
    previous_value,
    ROUND(
        ((current_value - previous_value) / previous_value * 100),
        2
    ) as change_percentage,
    target_value,
    ROUND(
        (current_value / target_value * 100),
        2
    ) as target_achievement
FROM (
    SELECT
        'Revenue' as metric_name,
        SUM(CASE WHEN period = 'current' THEN value END) as current_value,
        SUM(CASE WHEN period = 'previous' THEN value END) as previous_value,
        MAX(target) as target_value
    FROM kpi_data
    WHERE metric_type = 'financial'
)