-- 1. Top 3 customers by revenue in each country
WITH customer_rankings AS (
    SELECT 
        c.country,
        c.customer_id,
        c.customer_name,
        SUM(o.amount) AS total_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY c.country 
            ORDER BY SUM(o.amount) DESC
        ) AS rank_in_country
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY c.country, c.customer_id
)
SELECT *
FROM customer_rankings
WHERE rank_in_country <= 3
ORDER BY country, rank_in_country;

-- 2. Year-over-year growth by category
WITH yearly_sales AS (
    SELECT 
        p.category,
        YEAR(o.order_date) AS year,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status = 'completed'
    GROUP BY p.category, YEAR(o.order_date)
)
SELECT 
    category,
    year,
    revenue,
    LAG(revenue) OVER (PARTITION BY category ORDER BY year) AS prev_year_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY year)) / 
        LAG(revenue) OVER (PARTITION BY category ORDER BY year) * 100, 
        2
    ) AS yoy_growth_percent
FROM yearly_sales
ORDER BY category, year;

-- 3. Products that sell better on weekends
SELECT 
    p.product_id,
    p.product_name,
    SUM(CASE WHEN DAYOFWEEK(o.order_date) IN (1, 7) THEN oi.quantity ELSE 0 END) AS weekend_sales,
    SUM(CASE WHEN DAYOFWEEK(o.order_date) NOT IN (1, 7) THEN oi.quantity ELSE 0 END) AS weekday_sales,
    ROUND(
        SUM(CASE WHEN DAYOFWEEK(o.order_date) IN (1, 7) THEN oi.quantity ELSE 0 END) * 100.0 /
        SUM(oi.quantity), 2
    ) AS weekend_percentage
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.product_id
HAVING weekend_percentage > 40  -- More than 40% of sales on weekends
ORDER BY weekend_percentage DESC;

-- 4. Cohort analysis by signup month
SELECT 
    DATE_FORMAT(c.created_at, '%Y-%m') AS cohort_month,
    DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
    PERIOD_DIFF(
        DATE_FORMAT(o.order_date, '%Y%m'),
        DATE_FORMAT(c.created_at, '%Y%m')
    ) AS months_since_signup,
    COUNT(DISTINCT c.customer_id) AS customers,
    COUNT(o.order_id) AS orders,
    SUM(o.amount) AS revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY cohort_month, order_month
ORDER BY cohort_month, months_since_signup;

-- 5. Categories where all products are above average
SELECT 
    category,
    COUNT(*) AS product_count,
    MIN(price) AS min_price,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING MIN(price) > (SELECT AVG(price) FROM products);

-- 6. P&L summary with ROLLUP
SELECT 
    COALESCE(YEAR(order_date), 'TOTAL') AS year,
    COALESCE(QUARTER(order_date), 'Year Total') AS quarter,
    COALESCE(MONTH(order_date), 'Quarter Total') AS month,
    COUNT(*) AS transactions,
    SUM(revenue) AS gross_revenue,
    SUM(cost) AS total_cost,
    SUM(revenue - cost) AS gross_profit,
    ROUND(SUM(revenue - cost) / SUM(revenue) * 100, 2) AS margin_percent
FROM (
    SELECT 
        o.order_date,
        oi.quantity * oi.unit_price AS revenue,
        oi.quantity * p.cost AS cost
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status = 'completed'
) AS order_details
GROUP BY YEAR(order_date), QUARTER(order_date), MONTH(order_date) WITH ROLLUP;

-- 7. Seasonal patterns
SELECT 
    MONTH(order_date) AS month_num,
    MONTHNAME(order_date) AS month_name,
    COUNT(*) AS total_orders,
    AVG(amount) AS avg_order_value,
    SUM(amount) AS total_revenue,
    ROUND(
        SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 
        2
    ) AS percent_of_annual
FROM orders
WHERE status = 'completed'
GROUP BY MONTH(order_date), MONTHNAME(order_date)
ORDER BY month_num;

-- 8. Customer segmentation
SELECT 
    CASE 
        WHEN rfm_score >= 9 THEN 'Champions'
        WHEN rfm_score >= 7 THEN 'Loyal Customers'
        WHEN rfm_score >= 5 THEN 'Potential Loyalists'
        WHEN rfm_score >= 3 THEN 'At Risk'
        ELSE 'Lost'
    END AS segment,
    COUNT(*) AS customers,
    AVG(total_orders) AS avg_orders,
    AVG(total_spent) AS avg_revenue,
    AVG(days_since_last) AS avg_recency
FROM (
    SELECT 
        customer_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_spent,
        DATEDIFF(CURDATE(), MAX(order_date)) AS days_since_last,
        NTILE(3) OVER (ORDER BY DATEDIFF(CURDATE(), MAX(order_date))) AS recency_score,
        NTILE(3) OVER (ORDER BY COUNT(*)) AS frequency_score,
        NTILE(3) OVER (ORDER BY SUM(amount)) AS monetary_score,
        NTILE(3) OVER (ORDER BY DATEDIFF(CURDATE(), MAX(order_date))) +
        NTILE(3) OVER (ORDER BY COUNT(*)) +
        NTILE(3) OVER (ORDER BY SUM(amount)) AS rfm_score
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) AS customer_rfm
GROUP BY segment
ORDER BY rfm_score DESC;