SQL Formatter

-- Customer Analysis Query
-- This query demonstrates various SQL features including joins, aggregations, and window functions

WITH customer_stats AS (
    SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.email,
        c.registration_date,
        COUNT(o.order_id) AS total_orders,
        SUM(o.total_amount) AS total_spent,
        AVG(o.total_amount) AS avg_order_value,
        MAX(o.order_date) AS last_order_date,
        MIN(o.order_date) AS first_order_date,
        ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank
    FROM 
        customers c
    LEFT JOIN 
        orders o ON c.customer_id = o.customer_id
    WHERE 
        c.status = 'active'
        AND c.registration_date >= '2023-01-01'
    GROUP BY 
        c.customer_id, 
        c.first_name, 
        c.last_name, 
        c.email, 
        c.registration_date
    HAVING 
        COUNT(o.order_id) > 0
),
category_preferences AS (
    SELECT 
        o.customer_id,
        p.category,
        COUNT(*) AS category_orders,
        SUM(oi.quantity * oi.unit_price) AS category_spending,
        RANK() OVER (
            PARTITION BY o.customer_id 
            ORDER BY COUNT(*) DESC
        ) AS preference_rank
    FROM 
        orders o
    INNER JOIN 
        order_items oi ON o.order_id = oi.order_id
    INNER JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.order_date >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY 
        o.customer_id, p.category
)
SELECT 
    cs.customer_id,
    cs.first_name || ' ' || cs.last_name AS full_name,
    cs.email,
    cs.total_orders,
    ROUND(cs.total_spent, 2) AS total_spent,
    ROUND(cs.avg_order_value, 2) AS avg_order_value,
    cs.last_order_date,
    cs.spending_rank,
    cp.category AS preferred_category,
    cp.category_orders,
    ROUND(cp.category_spending, 2) AS category_spending,
    CASE 
        WHEN cs.total_spent >= 1000 THEN 'VIP'
        WHEN cs.total_spent >= 500 THEN 'Premium'
        WHEN cs.total_spent >= 100 THEN 'Regular'
        ELSE 'New'
    END AS customer_tier,
    CASE 
        WHEN cs.last_order_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
        WHEN cs.last_order_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'Inactive'
        ELSE 'Churned'
    END AS customer_status,
    DATEDIFF(cs.last_order_date, cs.first_order_date) AS customer_lifetime_days
FROM 
    customer_stats cs
LEFT JOIN 
    category_preferences cp ON cs.customer_id = cp.customer_id 
    AND cp.preference_rank = 1
WHERE 
    cs.spending_rank <= 100  -- Top 100 customers by spending
ORDER BY 
    cs.total_spent DESC,
    cs.last_order_date DESC
LIMIT 50;