Esta guía no es solo un listado de sintaxis, sino una referencia orientada a casos de uso reales en análisis de datos, reporting y backend.

1. Filtrado y Agrupación Avanzada

El pan de cada día: limpiar datos, filtrarlos y agruparlos para sacar métricas.

Caso de Uso: Análisis de Retención

Queremos saber cuántos clientes activos generaron más de 5 pedidos en lo que va de año.

SELECT 
  pais, 
  tipo_suscripcion, 
  COUNT(DISTINCT cliente_id) AS total_clientes,
  SUM(importe_total) AS ingresos
FROM facturacion
WHERE estado = 'PAGADO'
  AND fecha_factura >= '2024-01-01'
  AND pais IN ('ES', 'MX', 'AR')
GROUP BY pais, tipo_suscripcion
HAVING COUNT(pedido_id) > 5
ORDER BY ingresos DESC
LIMIT 100;

💡 Truco: WHERE filtra la tabla fila por fila antes de agrupar. HAVING filtra los resultados después de agrupar (ideal para filtrar por totales, medias o conteos).

---

2. JOINs (Cruzando Tablas)

Cruzar datos es vital. Entender la diferencia entre INNER y LEFT evita duplicar o perder datos financieros.

Caso de Uso: Clientes sin Pedidos

Queremos encontrar clientes registrados que nunca han llegado a comprar nada (para una campaña de marketing).

SELECT c.id, c.email, c.fecha_registro
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id
WHERE p.id IS NULL; -- La clave: si no hay pedido, el ID es nulo

Tipo	Qué hace	Caso de uso típico
INNER JOIN	Devuelve solo donde hay coincidencia.	Obtener facturas de un cliente específico.
LEFT JOIN	Devuelve todo el lado izquierdo, coincida o no.	Listar todos los usuarios, tengan o no compras.
FULL OUTER	Devuelve todo de ambos lados.	Conciliar dos bases de datos distintas.

---

3. CTEs (Common Table Expressions)

Las CTEs (WITH) son la mejor forma de organizar consultas complejas. Hacen que tu SQL se lea de arriba a abajo en lugar de tener subconsultas anidadas incomprensibles.

Caso de Uso: Embudo de Ventas (Funnel)

Paso 1: Sacar los carritos creados. Paso 2: Calcular la conversión.

WITH carritos AS (
  SELECT cliente_id, COUNT(*) AS total_creados
  FROM eventos
  WHERE tipo = 'add_to_cart'
  GROUP BY cliente_id
),
compras AS (
  SELECT cliente_id, COUNT(*) AS total_comprados
  FROM eventos
  WHERE tipo = 'purchase'
  GROUP BY cliente_id
)
SELECT 
  ca.cliente_id, 
  ca.total_creados, 
  COALESCE(co.total_comprados, 0) AS total_comprados,
  (COALESCE(co.total_comprados, 0) * 100.0 / ca.total_creados) AS pct_conversion
FROM carritos ca
LEFT JOIN compras co ON ca.cliente_id = co.cliente_id;

---

4. Funciones de Ventana (Window Functions)

La herramienta más poderosa para Analytics. Permiten hacer cálculos sobre un conjunto de filas sin colapsarlas (al contrario que GROUP BY).

Caso de Uso: Crecimiento Mensual (MoM)

Queremos comparar las ventas de este mes con las del mes anterior para calcular el porcentaje de crecimiento.

WITH ventas_mensuales AS (
  SELECT 
    DATE_TRUNC('month', fecha) AS mes,
    SUM(importe) AS ingresos
  FROM ventas
  GROUP BY 1
)
SELECT 
  mes, 
  ingresos,
  -- LAG lee la fila anterior (ordenada por mes)
  LAG(ingresos, 1) OVER (ORDER BY mes) AS ingresos_mes_anterior,
  -- Variación porcentual
  ((ingresos - LAG(ingresos, 1) OVER (ORDER BY mes)) / LAG(ingresos, 1) OVER (ORDER BY mes)) * 100 AS crecimiento_pct
FROM ventas_mensuales;

Otros usos comunes:

-- Encontrar la primera o última compra de un cliente
ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY fecha_compra DESC)

-- Calcular totales acumulados (Running Total)
SUM(ingresos) OVER (PARTITION BY año ORDER BY mes)

---

5. Manipulación de Datos (DML) y Seguridad

Al hacer scripts de mantenimiento o ETL, a veces hay que mover o actualizar datos masivamente.

Caso de Uso: Actualizar Precios por Categoría

-- Subir un 10% el precio a todos los productos de electrónica
UPDATE productos p
SET precio = precio * 1.10
FROM categorias c
WHERE p.categoria_id = c.id
  AND c.nombre = 'Electrónica';

UPSERT (Insertar o Actualizar)

Muy útil en pipelines de datos cuando no sabes si un registro ya existe.

INSERT INTO resumen_diario (fecha, total)
VALUES ('2024-05-03', 1500)
ON CONFLICT (fecha) DO UPDATE
  SET total = EXCLUDED.total;

---

6. Rendimiento y Optimización (Índices)

Una consulta lenta se suele arreglar con el índice correcto.

Caso de Uso: Dashboards Lentos

Si el dashboard filtra constantemente por cliente_id y ordena por fecha, un índice compuesto puede salvar el rendimiento.

-- Índice simple
CREATE INDEX idx_pedidos_fecha ON pedidos (fecha);

-- Índice compuesto (Regla de oro: primero Igualdad, luego Rangos/Orden)
CREATE INDEX idx_pedidos_cliente_fecha ON pedidos (cliente_id, fecha DESC);

-- Índice parcial (Si solo analizas usuarios activos, no indexes los borrados)
CREATE INDEX idx_usuarios_activos ON usuarios (fecha_ultimo_login)
WHERE estado = 'activo';

⚠️ Cuidado: Demasiados índices aceleran los SELECT, pero ralentizan los INSERT y UPDATE. Añade índices solo cuando haya un problema real de rendimiento en lecturas críticas.