SQL с помощью ИИ

-- AI generates something like:
SELECT 
    c.name,
    c.email,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT 10;

-- 'Show orders over $100 from the last month,
--  sorted by newest first'
SELECT *
FROM orders
WHERE amount > 100
  AND created_at >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY created_at DESC;

-- 'How many orders and how much revenue did we have
--  each month in 2025?'
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- 'For each product show the name, category,
--  total sales and number of customers'
SELECT 
    p.name AS product_name,
    p.category,
    SUM(o.amount) AS total_sales,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id, p.name, p.category
ORDER BY total_sales DESC;

-- 'For each customer show their monthly spending
--  and the average for their region in the same month'
SELECT 
    c.name,
    c.country,
    DATE_TRUNC('month', o.created_at) AS month,
    SUM(o.amount) AS customer_spending,
    AVG(SUM(o.amount)) OVER (
        PARTITION BY c.country, 
        DATE_TRUNC('month', o.created_at)
    ) AS region_avg
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY c.id, c.name, c.country, 
         DATE_TRUNC('month', o.created_at)
ORDER BY month, c.country;

-- AI generates CTEs + window functions:
WITH active_customers AS (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 3
),
monthly_sales AS (
    SELECT 
        p.category,
        DATE_TRUNC('month', o.created_at) AS month,
        SUM(o.amount) AS revenue
    FROM orders o
    JOIN products p ON o.product_id = p.id
    WHERE o.customer_id IN (
        SELECT customer_id FROM active_customers
    )
    AND o.created_at >= DATE_TRUNC('year', CURRENT_DATE)
    GROUP BY p.category, DATE_TRUNC('month', o.created_at)
)
SELECT 
    category,
    month,
    revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (
            PARTITION BY category ORDER BY month
        )) * 100.0 / NULLIF(LAG(revenue) OVER (
            PARTITION BY category ORDER BY month
        ), 0), 1
    ) AS growth_pct,
    SUM(revenue) OVER (
        PARTITION BY category 
        ORDER BY month
    ) AS ytd_revenue
FROM monthly_sales
ORDER BY category, month;