Olist E-commerceData Pipeline

-- Gold Layer: Customer Analytics Fact Table
WITH customer_orders AS (
    SELECT
        c.customer_id,
        c.customer_unique_id,
        c.customer_zip_code_prefix,
        c.customer_city,
        c.customer_state,
        COUNT(o.order_id) as total_orders,
        SUM(o.order_purchase_timestamp) as first_purchase,
        SUM(o.payment_value) as total_spent,
        AVG(o.payment_value) as avg_order_value,
        MAX(o.order_purchase_timestamp) as last_purchase,
        DATEDIFF(MAX(o.order_purchase_timestamp),
                MIN(o.order_purchase_timestamp)) as customer_lifespan_days
    FROM silver_customers c
    LEFT JOIN silver_orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_unique_id,
             c.customer_zip_code_prefix, c.customer_city, c.customer_state
)

SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix,
    customer_city,
    customer_state,
    total_orders,
    first_purchase,
    total_spent,
    avg_order_value,
    last_purchase,
    customer_lifespan_days,
    CASE
        WHEN total_orders >= 3 AND total_spent >= 500 THEN 'High Value'
        WHEN total_orders >= 2 THEN 'Regular'
        ELSE 'New'
    END as customer_segment
FROM customer_orders
QUALIFY ROW_NUMBER() OVER (ORDER BY total_spent DESC) <= 100000