Consultas SQL Avanzadas

Lleva tus habilidades de SQL al siguiente nivel dominando técnicas avanzadas de consultas que te permitirán realizar análisis complejos de datos.

JOINs Complejos

INNER JOIN con Múltiples Tablas

SELECT
    c.customer_name,
    o.order_date,
    p.product_name,
    od.quantity,
    od.quantity * p.unit_price as line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

LEFT JOIN para Análisis de Ausencias

-- Clientes que NO han hecho pedidos este año
SELECT
    c.customer_id,
    c.customer_name,
    c.country
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND YEAR(o.order_date) = 2024
WHERE o.order_id IS NULL;

SELF JOIN para Comparaciones

-- Encontrar empleados con el mismo cargo
SELECT
    e1.employee_name as empleado1,
    e2.employee_name as empleado2,
    e1.job_title
FROM employees e1
INNER JOIN employees e2 ON e1.job_title = e2.job_title
    AND e1.employee_id < e2.employee_id;

Subconsultas (Subqueries)

Subconsulta en WHERE

-- Productos con precio superior al promedio
SELECT
    product_name,
    unit_price
FROM products
WHERE unit_price > (
    SELECT AVG(unit_price)
    FROM products
)
ORDER BY unit_price DESC;

Subconsulta en SELECT

-- Ventas de cada producto vs promedio de su categoría
SELECT
    p.product_name,
    p.category,
    SUM(od.quantity * od.unit_price) as total_sales,
    (
        SELECT AVG(quantity * unit_price)
        FROM order_details od2
        INNER JOIN products p2 ON od2.product_id = p2.product_id
        WHERE p2.category = p.category
    ) as category_avg
FROM products p
INNER JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_name, p.category;

Subconsultas Correlacionadas

-- Empleados que ganan más que el promedio de su departamento
SELECT
    e.employee_name,
    e.department,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

Funciones de Ventana (Window Functions)

Ranking Functions

-- Ranking de productos por ventas en cada categoría
SELECT
    product_name,
    category,
    total_sales,
    RANK() OVER (
        PARTITION BY category
        ORDER BY total_sales DESC
    ) as category_rank,
    DENSE_RANK() OVER (
        PARTITION BY category
        ORDER BY total_sales DESC
    ) as dense_rank,
    ROW_NUMBER() OVER (
        PARTITION BY category
        ORDER BY total_sales DESC
    ) as row_num
FROM product_sales;

Aggregate Window Functions

-- Ventas acumuladas por mes
SELECT
    order_month,
    monthly_sales,
    SUM(monthly_sales) OVER (
        ORDER BY order_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sales,
    AVG(monthly_sales) OVER (
        ORDER BY order_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3months
FROM monthly_totals;

LAG y LEAD para Análisis Temporal

-- Comparación mes a mes
SELECT
    order_month,
    monthly_sales,
    LAG(monthly_sales, 1) OVER (ORDER BY order_month) as previous_month,
    monthly_sales - LAG(monthly_sales, 1) OVER (ORDER BY order_month) as month_over_month_change,
    LEAD(monthly_sales, 1) OVER (ORDER BY order_month) as next_month
FROM monthly_sales
ORDER BY order_month;

CTEs (Common Table Expressions)

CTE Básico

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as total_sales
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    total_sales,
    LAG(total_sales) OVER (ORDER BY month) as previous_month_sales,
    total_sales - LAG(total_sales) OVER (ORDER BY month) as growth
FROM monthly_sales
ORDER BY month;

CTEs Múltiples

WITH
customer_totals AS (
    SELECT
        customer_id,
        SUM(total_amount) as lifetime_value
    FROM orders
    GROUP BY customer_id
),
customer_segments AS (
    SELECT
        customer_id,
        lifetime_value,
        CASE
            WHEN lifetime_value >= 10000 THEN 'VIP'
            WHEN lifetime_value >= 5000 THEN 'Premium'
            ELSE 'Standard'
        END as segment
    FROM customer_totals
)
SELECT
    cs.segment,
    COUNT(*) as customer_count,
    AVG(cs.lifetime_value) as avg_ltv,
    SUM(cs.lifetime_value) as total_revenue
FROM customer_segments cs
GROUP BY cs.segment
ORDER BY total_revenue DESC;

CTE Recursivo

-- Estructura organizacional jerárquica
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: CEO (sin manager)
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 as level,
        CAST(employee_name AS VARCHAR(1000)) as path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: subordinados
    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1,
        eh.path || ' > ' || e.employee_name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    level,
    path
FROM employee_hierarchy
ORDER BY level, employee_name;

CASE Statements Avanzados

Segmentación de Clientes

SELECT
    customer_id,
    customer_name,
    total_purchases,
    CASE
        WHEN total_purchases >= 10000 THEN 'VIP'
        WHEN total_purchases >= 5000 THEN 'Premium'
        WHEN total_purchases >= 1000 THEN 'Regular'
        ELSE 'New'
    END as customer_tier,
    CASE
        WHEN last_purchase_date >= CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
        WHEN last_purchase_date >= CURRENT_DATE - INTERVAL '90 days' THEN 'At Risk'
        ELSE 'Churned'
    END as status
FROM customer_summary;

Optimización de Queries

Uso de EXPLAIN

EXPLAIN ANALYZE
SELECT
    c.customer_name,
    COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
HAVING COUNT(o.order_id) > 5;

Índices Efectivos

-- Crear índice para mejorar JOINs
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Índice compuesto para queries comunes
CREATE INDEX idx_orders_date_customer
ON orders(order_date, customer_id);

-- Índice parcial para datos activos
CREATE INDEX idx_active_orders
ON orders(customer_id)
WHERE status = 'active';

Técnicas de Agregación Avanzadas

GROUPING SETS

-- Múltiples niveles de agregación en una query
SELECT
    COALESCE(category, 'TOTAL') as category,
    COALESCE(product_name, 'CATEGORY TOTAL') as product_name,
    SUM(sales_amount) as total_sales
FROM product_sales
GROUP BY GROUPING SETS (
    (category, product_name),
    (category),
    ()
)
ORDER BY category, product_name;

CUBE y ROLLUP

-- ROLLUP para jerarquías
SELECT
    COALESCE(region, 'TOTAL') as region,
    COALESCE(country, 'REGION TOTAL') as country,
    COALESCE(city, 'COUNTRY TOTAL') as city,
    SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP (region, country, city);

Ejercicios Prácticos

Ejercicio 1: Análisis de Cohortes

Crea una query que analice la retención de clientes por cohorte mensual.

Ejercicio 2: RFM Analysis

Implementa un análisis RFM (Recency, Frequency, Monetary) usando window functions.

Ejercicio 3: Detección de Anomalías

Identifica transacciones anómalas usando desviaciones estándar y percentiles.

Mejores Prácticas

1. Usa CTEs para legibilidad: Prefiere CTEs sobre subqueries anidadas
2. Optimiza JOINs: Coloca las tablas más pequeñas primero
3. Aprovecha índices: Analiza execution plans regularmente
4. **Evita SELECT ***: Especifica solo las columnas necesarias
5. Documenta queries complejas: Usa comentarios explicativos

Conclusión

Dominar estas técnicas avanzadas de SQL te permitirá realizar análisis de datos sofisticados y eficientes. La clave está en la práctica constante y la comprensión de cuándo aplicar cada técnica.

Siguiente nivel: Explora SQL para Machine Learning y análisis estadístico avanzado.