Complex SQL Query Builder агент

-- Running totals and ranking with business context
WITH sales_analysis AS (
    SELECT 
        region,
        sales_date,
        revenue,
        -- Running total within each region
        SUM(revenue) OVER (
            PARTITION BY region 
            ORDER BY sales_date 
            ROWS UNBOUNDED PRECEDING
        ) AS running_total,
        -- Rank by revenue within region and month
        RANK() OVER (
            PARTITION BY region, DATE_TRUNC('month', sales_date)
            ORDER BY revenue DESC
        ) AS monthly_rank,
        -- Compare to previous period
        LAG(revenue, 1) OVER (
            PARTITION BY region 
            ORDER BY sales_date
        ) AS prev_revenue
    FROM sales_data
    WHERE sales_date >= '2024-01-01'
)
SELECT *,
       CASE 
           WHEN prev_revenue IS NULL THEN NULL
           ELSE ROUND(((revenue - prev_revenue) / prev_revenue * 100), 2)
       END AS growth_rate_pct
FROM sales_analysis;

-- Multi-level aggregation with conditional logic
WITH customer_metrics AS (
    SELECT 
        c.customer_id,
        c.segment,
        c.region,
        COUNT(DISTINCT o.order_id) as total_orders,
        SUM(CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days' 
            THEN o.order_value ELSE 0 END) as recent_revenue,
        SUM(o.order_value) as lifetime_revenue,
        AVG(o.order_value) as avg_order_value,
        MAX(o.order_date) as last_order_date
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.segment, c.region
),
segment_benchmarks AS (
    SELECT 
        segment,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY avg_order_value) as p75_aov,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY avg_order_value) as p25_aov
    FROM customer_metrics
    GROUP BY segment
)
SELECT 
    cm.*,
    sb.p75_aov,
    CASE 
        WHEN cm.avg_order_value >= sb.p75_aov THEN 'High Value'
        WHEN cm.avg_order_value <= sb.p25_aov THEN 'Low Value'
        ELSE 'Medium Value'
    END as value_tier,
    CASE 
        WHEN cm.last_order_date < CURRENT_DATE - INTERVAL '180 days' THEN 'At Risk'
        WHEN cm.recent_revenue > 0 THEN 'Active'
        ELSE 'Dormant'
    END as lifecycle_stage
FROM customer_metrics cm
JOIN segment_benchmarks sb ON cm.segment = sb.segment;

-- Optimized approach using EXISTS instead of IN for large datasets
SELECT p.product_id, p.product_name, p.category
FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM order_items oi 
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id 
    AND o.order_date >= '2024-01-01'
    AND o.status = 'completed'
)
AND p.status = 'active';

-- Using appropriate indexes:
-- CREATE INDEX idx_orders_date_status ON orders(order_date, status);
-- CREATE INDEX idx_order_items_product ON order_items(product_id, order_id);

-- Incremental processing pattern for large datasets
WITH batch_config AS (
    SELECT 
        DATE '2024-01-01' as start_date,
        DATE '2024-12-31' as end_date,
        INTERVAL '1 month' as batch_size
),
date_ranges AS (
    SELECT 
        generate_series(
            (SELECT start_date FROM batch_config),
            (SELECT end_date FROM batch_config),
            (SELECT batch_size FROM batch_config)
        ) as batch_start
),
batch_results AS (
    SELECT 
        dr.batch_start,
        dr.batch_start + INTERVAL '1 month' - INTERVAL '1 day' as batch_end,
        COUNT(*) as record_count,
        SUM(amount) as total_amount
    FROM date_ranges dr
    LEFT JOIN transactions t ON t.transaction_date >= dr.batch_start 
                           AND t.transaction_date < dr.batch_start + INTERVAL '1 month'
    GROUP BY dr.batch_start
)
SELECT *,
       SUM(total_amount) OVER (ORDER BY batch_start) as cumulative_total
FROM batch_results
ORDER BY batch_start;

-- Customer cohort retention analysis
WITH first_purchase AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),
user_activities AS (
    SELECT 
        fp.customer_id,
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) as activity_month,
        EXTRACT(EPOCH FROM (DATE_TRUNC('month', o.order_date) - fp.cohort_month)) / (30.44 * 24 * 3600) as period_number
    FROM first_purchase fp
    JOIN orders o ON fp.customer_id = o.customer_id
),
cohort_table AS (
    SELECT 
        cohort_month,
        period_number,
        COUNT(DISTINCT customer_id) as active_customers
    FROM user_activities
    GROUP BY cohort_month, period_number
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT customer_id) as cohort_size
    FROM first_purchase
    GROUP BY cohort_month
)
SELECT 
    ct.cohort_month,
    ct.period_number,
    ct.active_customers,
    cs.cohort_size,
    ROUND(100.0 * ct.active_customers / cs.cohort_size, 2) as retention_rate
FROM cohort_table ct
JOIN cohort_sizes cs ON ct.cohort_month = cs.cohort_month
ORDER BY ct.cohort_month, ct.period_number;

-- Query with comprehensive error handling and data validation
WITH validated_data AS (
    SELECT 
        customer_id,
        order_date,
        order_value,
        CASE 
            WHEN order_value <= 0 THEN 'Invalid: Non-positive value'
            WHEN order_date > CURRENT_DATE THEN 'Invalid: Future date'
            WHEN customer_id IS NULL THEN 'Invalid: Missing customer'
            ELSE 'Valid'
        END as validation_status
    FROM raw_orders
    WHERE order_date >= '2024-01-01'
),
quality_summary AS (
    SELECT 
        validation_status,
        COUNT(*) as record_count,
        COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
    FROM validated_data
    GROUP BY validation_status
)
-- Main analysis on clean data
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(order_value) as total_value,
    AVG(order_value) as avg_value
FROM validated_data
WHERE validation_status = 'Valid'
GROUP BY customer_id
HAVING COUNT(*) >= 2  -- Only customers with multiple orders
ORDER BY total_value DESC;