Queries SQL

Queries SQL

Las 5 queries principales del archivo sales_analysis.sql cubren los análisis más comunes en un entorno de SalesOps / RevOps.

---

Query 1 — Revenue total y margen por región

SELECT
    region,
    SUM(revenue)                             AS total_revenue,
    SUM(cost)                                AS total_cost,
    SUM(revenue - cost)                      AS total_profit,
    ROUND(
        (SUM(revenue - cost) / SUM(revenue)) * 100, 2
    )                                        AS profit_margin_pct
FROM sales
GROUP BY region
ORDER BY total_revenue DESC;

Insight: identifica qué regiones generan más margen real, no solo más volumen.

---

Query 2 — Top 10 vendedores por ingreso

SELECT
    salesperson,
    COUNT(*)                    AS deals_closed,
    SUM(revenue)                AS total_revenue,
    AVG(revenue)                AS avg_deal_size,
    ROUND(AVG(profit_margin), 2) AS avg_margin_pct
FROM sales
GROUP BY salesperson
ORDER BY total_revenue DESC
LIMIT 10;

Insight: separa volumen de deals vs tamaño de deal — un vendedor puede cerrar muchos deals pequeños vs pocos deals grandes.

---

Query 3 — Tendencia mensual de ventas

SELECT
    strftime('%Y-%m', sale_date)   AS month,
    COUNT(*)                       AS deals,
    SUM(revenue)                   AS revenue,
    SUM(revenue - cost)            AS profit
FROM sales
GROUP BY strftime('%Y-%m', sale_date)
ORDER BY month;

Para PostgreSQL: reemplazar strftime('%Y-%m', sale_date) por TO_CHAR(sale_date, 'YYYY-MM')

---

Query 4 — Productos con margen bajo (candidatos a revisión)

SELECT
    product,
    SUM(revenue)                                   AS total_revenue,
    ROUND(AVG(profit_margin), 2)                   AS avg_margin_pct,
    COUNT(*)                                       AS units_sold
FROM sales
GROUP BY product
HAVING AVG(profit_margin) < 20   -- umbral: 20% de margen
ORDER BY avg_margin_pct ASC;

Acción: productos con margen < 20% requieren revisión de precios o reducción de costos.

---

Query 5 — Estacionalidad de ventas por trimestre

SELECT
    CASE
        WHEN strftime('%m', sale_date) IN ('01','02','03') THEN 'Q1'
        WHEN strftime('%m', sale_date) IN ('04','05','06') THEN 'Q2'
        WHEN strftime('%m', sale_date) IN ('07','08','09') THEN 'Q3'
        ELSE 'Q4'
    END                AS quarter,
    SUM(revenue)       AS total_revenue,
    COUNT(*)           AS total_deals
FROM sales
GROUP BY quarter
ORDER BY quarter;