Customer Value Analysis

WITH valid_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        shipping_cost
    FROM orders
    WHERE LOWER(order_status) <> 'canceled'
       OR order_status IS NULL
),

customer_order_summary AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.region,
        c.customer_segment,
        MIN(vo.order_date) AS first_order_date,
        MAX(vo.order_date) AS last_order_date,
        COUNT(DISTINCT vo.order_id) AS order_count
    FROM customers c
    LEFT JOIN valid_orders vo
        ON c.customer_id = vo.customer_id
    GROUP BY
        c.customer_id,
        c.customer_name,
        c.region,
        c.customer_segment
),

customer_product_metrics AS (
    SELECT
        c.customer_id,
        COALESCE(SUM(oi.quantity), 0) AS total_quantity,
        COALESCE(
            SUM(oi.quantity * oi.unit_price), 0
        ) AS total_revenue
    FROM customers c
    LEFT JOIN valid_orders vo
        ON c.customer_id = vo.customer_id
    LEFT JOIN order_items oi
        ON vo.order_id = oi.order_id
    GROUP BY c.customer_id
),

customer_shipping AS (
    SELECT
        customer_id,
        SUM(shipping_cost) AS total_shipping_cost
    FROM valid_orders
    GROUP BY customer_id
),

customer_metrics AS (
    SELECT
        cos.customer_id,
        cos.customer_name,
        cos.region,
        cos.customer_segment,
        cos.first_order_date,
        cos.last_order_date,
        cos.order_count,
        cpm.total_quantity,
        cpm.total_revenue,
        COALESCE(cs.total_shipping_cost, 0)
            AS total_shipping_cost,
        CASE
            WHEN cos.order_count = 0 THEN 0
            ELSE cpm.total_revenue / cos.order_count
        END AS average_order_value
    FROM customer_order_summary cos
    LEFT JOIN customer_product_metrics cpm
        ON cos.customer_id = cpm.customer_id
    LEFT JOIN customer_shipping cs
        ON cos.customer_id = cs.customer_id
),

customer_rankings AS (
    SELECT
        *,
        RANK() OVER (
            ORDER BY total_revenue DESC
        ) AS customer_rank,

        RANK() OVER (
            PARTITION BY region
            ORDER BY total_revenue DESC
        ) AS regional_rank,

        ROW_NUMBER() OVER (
            PARTITION BY region
            ORDER BY
                total_revenue DESC,
                order_count DESC,
                customer_id
        ) AS regional_row_number,

        SUM(total_revenue) OVER ()
            AS overall_revenue

    FROM customer_metrics
),

final_metrics AS (
    SELECT
        *,
        CASE
            WHEN overall_revenue = 0 THEN 0
            ELSE 100.0 * total_revenue / overall_revenue
        END AS percentage_of_total_revenue,

        DATE_DIFF(
            'day',
            CAST(last_order_date AS DATE),
            CURRENT_DATE
        ) AS days_since_last_order

    FROM customer_rankings
)

SELECT
    customer_id,
    customer_name,
    region,
    customer_segment,
    first_order_date,
    last_order_date,
    order_count,
    total_quantity,
    ROUND(total_revenue, 2) AS total_revenue,
    ROUND(average_order_value, 2) AS average_order_value,
    customer_rank,
    regional_rank,
    ROUND(percentage_of_total_revenue, 2)
        AS percentage_of_total_revenue,
    days_since_last_order,

    CASE
        WHEN order_count = 0 THEN 'No Orders'
        WHEN customer_rank <= 10 THEN 'Top 10 Customer'
        WHEN percentage_of_total_revenue >= 1
            THEN 'High Value'
        WHEN percentage_of_total_revenue >= 0.1
            THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_value_segment

FROM final_metrics
ORDER BY
    customer_rank,
    customer_id;

WITH item_returns AS (
    SELECT o.customer_id,
           SUM(oi.quantity)                       AS quantity_bought,
           SUM(COALESCE(r.return_quantity, 0))    AS quantity_returned
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN returns r ON oi.order_item_id = r.order_item_id
    GROUP BY o.customer_id
)
SELECT
    cs.customer_value_segment                                   AS segment,
    COUNT(*)                                                    AS customers,
    ROUND(SUM(cs.total_revenue), 2)                             AS revenue,
    ROUND(100.0 * SUM(cs.total_revenue)
          / SUM(SUM(cs.total_revenue)) OVER (), 1)              AS pct_of_revenue,
    ROUND(AVG(cs.order_count), 1)                               AS avg_orders,
    ROUND(SUM(cs.total_revenue) / NULLIF(SUM(cs.order_count), 0), 2) AS avg_order_value,
    ROUND(MEDIAN(cs.days_since_last_order), 0)                  AS median_days_since_last_order,
    ROUND(100.0 * SUM(ir.quantity_returned)
          / NULLIF(SUM(ir.quantity_bought), 0), 1)              AS return_rate,
    ROUND(100.0 * COUNT(*) FILTER (WHERE cs.customer_segment = 'Corporate')
          / COUNT(*), 1)                                        AS pct_corporate
FROM customer_segments cs
LEFT JOIN item_returns ir ON cs.customer_id = ir.customer_id
GROUP BY cs.customer_value_segment
ORDER BY MIN(cs.customer_rank);

SELECT
    cs.customer_value_segment AS segment,
    p.category,
    ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
          / SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY cs.customer_value_segment), 1) AS pct_of_segment_revenue
FROM customer_segments cs
JOIN orders o       ON cs.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
GROUP BY cs.customer_value_segment, p.category
ORDER BY segment, pct_of_segment_revenue DESC;