CTE Query Generator агент

WITH cleaned_data AS (
  SELECT 
    customer_id,
    UPPER(TRIM(customer_name)) AS customer_name,
    CASE 
      WHEN email LIKE '%@%' THEN LOWER(email)
      ELSE NULL 
    END AS email,
    DATE(order_date) AS order_date
  FROM raw_orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
),
aggregated_metrics AS (
  SELECT 
    customer_id,
    customer_name,
    COUNT(*) AS order_count,
    SUM(order_amount) AS total_spent,
    AVG(order_amount) AS avg_order_value,
    MAX(order_date) AS last_order_date
  FROM cleaned_data
  GROUP BY customer_id, customer_name
),
customer_segments AS (
  SELECT *,
    CASE 
      WHEN total_spent > 1000 THEN 'High Value'
      WHEN total_spent > 500 THEN 'Medium Value'
      ELSE 'Low Value'
    END AS customer_segment
  FROM aggregated_metrics
)
SELECT * FROM customer_segments
ORDER BY total_spent DESC;

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(sales_amount) AS monthly_revenue
  FROM sales_data
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY month ORDER BY monthly_revenue DESC) AS category_rank,
    LAG(monthly_revenue) OVER (PARTITION BY product_category ORDER BY month) AS prev_month_revenue
  FROM monthly_sales
),
growth_analysis AS (
  SELECT *,
    CASE 
      WHEN prev_month_revenue IS NOT NULL 
      THEN ((monthly_revenue - prev_month_revenue) / prev_month_revenue) * 100
      ELSE NULL
    END AS growth_rate
  FROM ranked_categories
)
SELECT * FROM growth_analysis
WHERE category_rank <= 3;

WITH RECURSIVE employee_hierarchy AS (
  -- Anchor: Start with top-level managers
  SELECT 
    employee_id,
    employee_name,
    manager_id,
    job_title,
    1 AS level,
    CAST(employee_name AS VARCHAR(1000)) AS hierarchy_path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: Add direct reports
  SELECT 
    e.employee_id,
    e.employee_name,
    e.manager_id,
    e.job_title,
    eh.level + 1,
    eh.hierarchy_path || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
  WHERE eh.level < 10  -- Prevent infinite recursion
)
SELECT 
  REPEAT('  ', level - 1) || employee_name AS indented_name,
  level,
  hierarchy_path,
  job_title
FROM employee_hierarchy
ORDER BY hierarchy_path;

WITH RECURSIVE date_series AS (
  SELECT DATE('2024-01-01') AS date_value

  UNION ALL

  SELECT date_value + INTERVAL '1 day'
  FROM date_series
  WHERE date_value < DATE('2024-12-31')
),
sales_with_gaps AS (
  SELECT 
    ds.date_value,
    COALESCE(s.daily_sales, 0) AS daily_sales,
    COALESCE(s.transaction_count, 0) AS transaction_count
  FROM date_series ds
  LEFT JOIN (
    SELECT 
      DATE(order_date) AS order_date,
      SUM(order_amount) AS daily_sales,
      COUNT(*) AS transaction_count
    FROM orders
    GROUP BY DATE(order_date)
  ) s ON ds.date_value = s.order_date
)
SELECT * FROM sales_with_gaps
ORDER BY date_value;

WITH base_metrics AS (
  SELECT 
    product_id,
    SUM(quantity_sold) AS total_quantity,
    SUM(revenue) AS total_revenue
  FROM sales_data
  WHERE sale_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY product_id
),
top_products AS (
  SELECT product_id
  FROM base_metrics
  WHERE total_revenue > (
    SELECT AVG(total_revenue) * 1.5 FROM base_metrics
  )
),
performance_comparison AS (
  SELECT 
    bm.*,
    tp.product_id IS NOT NULL AS is_top_performer
  FROM base_metrics bm
  LEFT JOIN top_products tp ON bm.product_id = tp.product_id
)
SELECT * FROM performance_comparison
ORDER BY total_revenue DESC;