Análise de Vendas com SQL: do básico ao avançado

-- Clientes
customers (id, name, city, created_at)

-- Pedidos
orders (id, customer_id, total, status, created_at)

-- Itens do pedido
order_items (id, order_id, product, category, quantity, price)

SELECT
    COUNT(DISTINCT customer_id) AS total_clientes,
    COUNT(*)                    AS total_pedidos,
    SUM(total)                  AS receita_total,
    AVG(total)                  AS ticket_medio,
    MIN(created_at)             AS primeiro_pedido,
    MAX(created_at)             AS ultimo_pedido
FROM orders
WHERE status = 'completed';

WITH receita_mensal AS (
    SELECT
        DATE_TRUNC('month', created_at) AS mes,
        SUM(total)                       AS receita
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
)
SELECT
    mes,
    receita,
    LAG(receita) OVER (ORDER BY mes)                        AS receita_mes_anterior,
    ROUND(
        (receita - LAG(receita) OVER (ORDER BY mes))
        / LAG(receita) OVER (ORDER BY mes) * 100, 2
    )                                                        AS crescimento_pct
FROM receita_mensal
ORDER BY mes;

SELECT
    c.name,
    c.city,
    COUNT(o.id)    AS total_pedidos,
    SUM(o.total)   AS receita_total,
    AVG(o.total)   AS ticket_medio,
    RANK() OVER (ORDER BY SUM(o.total) DESC) AS ranking
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.id, c.name, c.city
ORDER BY receita_total DESC
LIMIT 10;

SELECT
    oi.category,
    COUNT(DISTINCT o.id)    AS total_pedidos,
    SUM(oi.quantity)        AS unidades_vendidas,
    SUM(oi.price * oi.quantity) AS receita_total,
    ROUND(
        SUM(oi.price * oi.quantity)
        / SUM(SUM(oi.price * oi.quantity)) OVER () * 100, 1
    ) AS participacao_pct
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY oi.category
ORDER BY receita_total DESC;

WITH ultima_compra AS (
    SELECT
        customer_id,
        MAX(created_at) AS ultima_compra,
        COUNT(*)        AS total_pedidos,
        SUM(total)      AS receita_total
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    c.name,
    c.city,
    uc.ultima_compra,
    uc.total_pedidos,
    uc.receita_total,
    DATE_PART('day', NOW() - uc.ultima_compra) AS dias_sem_comprar
FROM ultima_compra uc
JOIN customers c ON uc.customer_id = c.id
WHERE uc.ultima_compra < NOW() - INTERVAL '90 days'
  AND uc.receita_total > 500   -- apenas clientes relevantes
ORDER BY uc.receita_total DESC;