Queries SQL

Queries SQL

1. Regiones más rentables

SELECT
    region,
    SUM(sales) AS total_sales,
    SUM(profit) AS total_profit,
    ROUND(SUM(profit) / SUM(sales) * 100, 2) AS profit_margin_pct
FROM orders
GROUP BY region
ORDER BY total_profit DESC;

2. Top vendedores por revenue

SELECT
    salesperson,
    COUNT(order_id) AS total_orders,
    SUM(sales) AS total_revenue,
    AVG(profit) AS avg_profit_per_order
FROM orders
GROUP BY salesperson
ORDER BY total_revenue DESC
LIMIT 10;

3. Estacionalidad de ventas

SELECT
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(YEAR FROM order_date) AS year,
    SUM(sales) AS monthly_sales,
    LAG(SUM(sales)) OVER (ORDER BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)) AS prev_month,
    ROUND((SUM(sales) - LAG(SUM(sales)) OVER (...)) / LAG(SUM(sales)) OVER (...) * 100, 1) AS mom_growth_pct
FROM orders
GROUP BY 1, 2;

4. Productos con bajo margen

SELECT
    product_name,
    category,
    SUM(sales) AS total_sales,
    SUM(profit) AS total_profit,
    ROUND(SUM(profit)/SUM(sales)*100, 2) AS margin_pct
FROM orders
GROUP BY product_name, category
HAVING margin_pct < 5
ORDER BY total_sales DESC;

5. Impacto del descuento en profit

SELECT
    CASE
        WHEN discount = 0 THEN 'Sin descuento'
        WHEN discount <= 0.2 THEN 'Descuento bajo (≤20%)'
        WHEN discount <= 0.4 THEN 'Descuento medio (≤40%)'
        ELSE 'Descuento alto (>40%)'
    END AS discount_tier,
    COUNT(*) AS orders,
    ROUND(AVG(profit), 2) AS avg_profit,
    ROUND(AVG(profit/sales*100), 2) AS avg_margin_pct
FROM orders
GROUP BY 1
ORDER BY avg_profit DESC;