-- Via Snowsight UI or API
-- User: "Show me the top 10 customers by revenue in Q4 2024"
-- Copilot generates:

SELECT
  c.customer_name,
  SUM(o.order_amount) as total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-10-01'
  AND o.order_date <= '2024-12-31'
GROUP BY c.customer_name
ORDER BY total_revenue DESC
LIMIT 10;

-- User: "What's the month-over-month growth rate for each product category?"
-- Copilot generates:

WITH monthly_sales AS (
  SELECT
    p.category,
    DATE_TRUNC('month', o.order_date) as month,
    SUM(o.quantity * o.unit_price) as revenue
  FROM products p
  JOIN order_items o ON p.product_id = o.product_id
  GROUP BY 1, 2
)
SELECT
  category,
  month,
  revenue,
  LAG(revenue) OVER (PARTITION BY category ORDER BY month) as prev_month,
  ROUND(((revenue - prev_month) / prev_month) * 100, 2) as growth_pct
FROM monthly_sales
ORDER BY category, month;

-- After running a query, Copilot suggests:
-- "Would you like to:"
-- 1. "Add a trend line to visualize the pattern?"
-- 2. "Compare this with last year's data?"
-- 3. "Break this down by region?"
-- 4. "Export this as a CSV?"

-- Example follow-up query
SELECT
  category,
  month,
  revenue,
  CASE
    WHEN revenue > LAG(revenue) OVER (PARTITION BY category ORDER BY month)
    THEN 'Up'
    WHEN revenue < LAG(revenue) OVER (PARTITION BY category ORDER BY month)
    THEN 'Down'
    ELSE 'Flat'
  END as trend
FROM monthly_sales
WHERE EXTRACT(YEAR FROM month) = 2024;

-- Copilot understands your schema
-- User: "Show me customer churn analysis"
-- Copilot identifies relevant tables:

SELECT
  c.customer_id,
  c.customer_name,
  MAX(o.order_date) as last_order_date,
  DATEDIFF(day, MAX(o.order_date), CURRENT_DATE()) as days_since_last_order,
  CASE
    WHEN DATEDIFF(day, MAX(o.order_date), CURRENT_DATE()) > 90
    THEN 'At Risk'
    WHEN DATEDIFF(day, MAX(o.order_date), CURRENT_DATE()) > 180
    THEN 'Churned'
    ELSE 'Active'
  END as status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

-- Copilot learns business terminology
-- User: "Show me gold customers who are at risk"
-- Copilot understands:
-- - "gold customers" = customers with loyalty_tier = 'GOLD'
-- - "at risk" = haven't ordered in 60+ days

SELECT
  c.customer_id,
  c.customer_name,
  c.email,
  c.loyalty_tier,
  MAX(o.order_date) as last_order,
  SUM(o.order_amount) as lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.loyalty_tier = 'GOLD'
  AND DATEDIFF(day, MAX(o.order_date), CURRENT_DATE()) > 60
GROUP BY c.customer_id, c.customer_name, c.email, c.loyalty_tier;

-- Copilot analyzes and suggests improvements
-- Original query: SELECT * FROM large_table WHERE date > '2024-01-01'

-- Copilot suggests:
-- 1. "Add a clustering key on 'date' column for better performance"
-- 2. "Consider using partition pruning with specific date range"
-- 3. "This table has 1B rows - consider adding a warehouse size hint"

-- Optimized query
SELECT
  customer_id,
  order_amount,
  order_date
FROM large_table
WHERE date >= '2024-01-01'
  AND date < '2024-02-01'
CLUSTER BY (date);

-- Copilot identifies anomalies
-- User: "Are there any unusual patterns in my data?"

WITH daily_stats AS (
  SELECT
    DATE_TRUNC('day', order_date) as day,
    COUNT(*) as order_count,
    AVG(order_amount) as avg_amount
  FROM orders
  WHERE order_date >= DATEADD(month, -3, CURRENT_DATE())
  GROUP BY 1
),
stats_with_zscore AS (
  SELECT
    day,
    order_count,
    avg_amount,
    (order_count - AVG(order_count) OVER()) / STDDEV(order_count) OVER() as z_score
  FROM daily_stats
)
SELECT *
FROM stats_with_zscore
WHERE ABS(z_score) > 2
ORDER BY day;