Generate SQL CTEs for Complex Queries

As a data analyst, I want to efficiently break down complex SQL queries into manageable, readable, and reusable components so that I can improve data analysis workflows and maintainability.

Big Job: Simplify and optimize complex SQL query development for enhanced data analysis and maintainability.

Small Job: Generate SQL Common Table Expressions (CTEs), including recursive and non-recursive patterns, to structure and clarify data transformations and analytical logic.

Example Usage:

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;