-- Query customer information for support agents
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.subscription_tier,
    c.signup_date,
    COUNT(t.ticket_id) as total_tickets,
    MAX(t.created_at) as last_ticket_date
FROM customers c
LEFT JOIN support_tickets t ON c.customer_id = t.customer_id
WHERE c.email = ?
GROUP BY c.customer_id, c.name, c.email, c.subscription_tier, c.signup_date;

-- Revenue analysis for business intelligence agents
SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(total_amount) as monthly_revenue,
    COUNT(*) as order_count,
    AVG(total_amount) as avg_order_value,
    SUM(total_amount) - LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as revenue_growth
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

// MongoDB query for inventory tracking
{
  "pipeline": [
    {
      "$match": {
        "product_id": "{{product_id}}",
        "warehouse": "{{warehouse_location}}"
      }
    },
    {
      "$group": {
        "_id": "$product_id",
        "total_quantity": { "$sum": "$quantity" },
        "reserved_quantity": { 
          "$sum": { 
            "$cond": [{ "$eq": ["$status", "reserved"] }, "$quantity", 0] 
          }
        },
        "available_quantity": {
          "$sum": { 
            "$cond": [{ "$eq": ["$status", "available"] }, "$quantity", 0] 
          }
        }
      }
    }
  ]
}

-- User engagement analysis for marketing agents
WITH user_metrics AS (
  SELECT 
    user_id,
    COUNT(DISTINCT session_id) as total_sessions,
    AVG(session_duration) as avg_session_duration,
    COUNT(DISTINCT DATE(event_timestamp)) as active_days,
    MAX(event_timestamp) as last_activity
  FROM user_events
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT 
  um.*,
  CASE 
    WHEN active_days >= 20 THEN 'High'
    WHEN active_days >= 10 THEN 'Medium'
    ELSE 'Low'
  END as engagement_level
FROM user_metrics um
WHERE last_activity >= CURRENT_DATE - INTERVAL '7 days';

-- Customer 360 view combining multiple sources
SELECT 
    c.customer_id,
    c.name,
    c.email,
    s.subscription_status,
    s.mrr,
    t.total_tickets,
    t.avg_response_time,
    m.last_marketing_touch
FROM crm.customers c
LEFT JOIN billing.subscriptions s ON c.customer_id = s.customer_id
LEFT JOIN support.ticket_summary t ON c.customer_id = t.customer_id
LEFT JOIN marketing.customer_touches m ON c.customer_id = m.customer_id
WHERE c.customer_id = ?;

-- Product performance across channels
SELECT 
    p.product_name,
    p.category,
    SUM(oi.quantity) as total_sold,
    SUM(oi.quantity * oi.price) as total_revenue,
    AVG(r.rating) as avg_rating,
    COUNT(DISTINCT o.customer_id) as unique_customers
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN reviews r ON p.product_id = r.product_id
WHERE o.order_date >= ?
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC;

-- Monthly financial summary
SELECT 
    DATE_TRUNC('month', transaction_date) as month,
    account_type,
    SUM(CASE WHEN transaction_type = 'credit' THEN amount ELSE 0 END) as total_credits,
    SUM(CASE WHEN transaction_type = 'debit' THEN amount ELSE 0 END) as total_debits,
    SUM(CASE WHEN transaction_type = 'credit' THEN amount ELSE -amount END) as net_amount
FROM financial_transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', transaction_date), account_type
ORDER BY month, account_type;