Lección 07 — SQL avanzado

Stack: PostgreSQL · Proyecto: TicketFlow Estado: Publicada — impartición cuando entregues la anterior Prerrequisito: Lección 06 — Git profesional

Objetivos

1. Escribir consultas con CTEs y subconsultas legibles y componibles.
2. Usar funciones de ventana para rankings y acumulados sin cortar las filas.
3. Dominar joins y agregaciones con criterio (y saber cuándo el JOIN duplica).
4. Resolver las tres consultas dominantes de TicketFlow en SQL puro.

1. CTEs: legibilidad antes que astucia

Un CTE (WITH) nombra un paso intermedio. Encadena pasos como un pipeline:

WITH reservas_activas AS (
    SELECT user_id, COUNT(*) AS n
    FROM reservation
    WHERE status IN ('PENDING_PAYMENT', 'CONFIRMED')
    GROUP BY user_id
)
SELECT u.email, ra.n
FROM reservas_activas ra
JOIN "user" u ON u.id = ra.user_id
WHERE ra.n >= 3
ORDER BY ra.n DESC;

Regla: si una consulta necesita un comentario para explicar "qué es esa subconsulta", es un CTE. En PostgreSQL 12+ los CTE no recursivos se inlinean (mismo plan que la subconsulta), así que no pagas legibilidad.

2. Funciones de ventana: el ranking sin GROUP BY

GROUP BY colapsa filas; una función de ventana añade un cálculo por fila sobre una "ventana":

-- Top de eventos por ventas, con ranking y parte del total
SELECT e.title,
       SUM(ri.price_at_purchase)              AS vendido,
       RANK()       OVER (ORDER BY SUM(ri.price_at_purchase) DESC) AS rank_,
       SUM(ri.price_at_purchase) / SUM(SUM(ri.price_at_purchase)) OVER () AS pct
FROM reservation_item ri
JOIN reservation r ON r.id = ri.reservation_id
JOIN event e       ON e.id = r.event_id
WHERE r.status = 'CONFIRMED'
GROUP BY e.title;

Las que usarás: ROW_NUMBER() (paginación sin saltos), RANK()/DENSE_RANK() (top con empates), LAG()/LEAD() (diferencia con el periodo anterior), SUM(...) OVER (PARTITION BY... ORDER BY...) (acumulado).

3. Joins y agregaciones: el duplicado silencioso

JOIN contra una tabla 1:N y luego SUM cuenta mal si no distingues el nivel:

-- MAL: el JOIN a items duplica las filas de payment → doble conteo
SELECT r.id, SUM(p.amount), SUM(ri.price_at_purchase)
FROM reservation r
JOIN payment p          ON p.reservation_id = r.id
JOIN reservation_item ri ON ri.reservation_id = r.id
GROUP BY r.id;

-- BIEN: agrega cada relación en su CTE y luego une resultados
WITH pagos AS (
  SELECT reservation_id, SUM(amount) AS total FROM payment GROUP BY reservation_id
),
items AS (
  SELECT reservation_id, SUM(price_at_purchase) AS total FROM reservation_item GROUP BY reservation_id
)
SELECT r.id, pg.total, it.total
FROM reservation r
JOIN pagos pg ON pg.reservation_id = r.id
JOIN items it ON it.reservation_id = r.id;

Tipos de JOIN en una línea: INNER (intersección), LEFT (todo el de la izquierda, NULL si no hay pareja — para "eventos sin ventas"), EXISTS (solo saber si hay pareja: más barato que LEFT + IS NULL).

4. Las tres consultas de TicketFlow en SQL

-- 1. Disponibilidad de un evento (asientos sin reserva activa ni confirmada)
SELECT s.id
FROM seat s
WHERE s.event_id = %s
  AND NOT EXISTS (
    SELECT 1 FROM reservation_item ri
    JOIN reservation r ON r.id = ri.reservation_id
    WHERE ri.seat_id = s.id
      AND r.status IN ('PENDING_PAYMENT', 'CONFIRMED')
  )
ORDER BY s.sector, s.row, s.number;

-- 2. Ingresos por día de los últimos 30 (serie completa, con días sin ventas)
SELECT d::date AS dia, COALESCE(SUM(p.amount), 0) AS ingresos
FROM generate_series(CURRENT_DATE - 29, CURRENT_DATE, interval '1 day') d
LEFT JOIN payment p ON p.created_at::date = d::date AND p.status = 'SUCCEEDED'
GROUP BY d ORDER BY d;

-- 3. Tasa de conversión por evento (reservas confirmadas / creadas)
SELECT e.title,
       COUNT(r.id) FILTER (WHERE r.status = 'CONFIRMED')::float
       / NULLIF(COUNT(r.id), 0) AS conversion
FROM event e LEFT JOIN reservation r ON r.event_id = e.id
GROUP BY e.title;

Herramientas nuevas de estas consultas: EXISTS/NOT EXISTS, generate_series (series temporales), FILTER (agregación condicional), COALESCE/NULLIF (valores por defecto y división por cero).

5. CTEs recursivas y consulta de inventario con hops

Los árboles (categorías, carpetas, jerarquía de venues/organizadores) NO se recorren con N queries: se recorren con UNA CTE recursiva. El caso de TicketFlow: la organización del 08 tiene sub-organizaciones; listar todas las ventas de un árbol entero:

WITH RECURSIVE org_tree AS (
    -- caso base: la organización raíz
    SELECT id, parent_id, name FROM org WHERE id = :root_id
    UNION ALL
    -- paso recursivo: los hijos de lo ya visitado
    SELECT o.id, o.parent_id, o.name
    FROM org o
    JOIN org_tree t ON o.parent_id = t.id
)
SELECT o.name, COUNT(r.id) AS ventas, SUM(r.total_amount) AS recaudado
FROM org_tree t
JOIN org o ON o.id = t.id
LEFT JOIN reservations r ON r.org_id = t.id AND r.status = 'CONFIRMED'
GROUP BY o.name
ORDER BY recaudado DESC NULLS LAST;

Tres avisos de producción: (1) el recursivo puede LOOPEAR INFINITO con datos corruptos (un ciclo en parent_id): añade una guardia (WHERE depth < 20 con una columna depth incrementada en el paso recursivo); (2) sin índice en parent_id, cada paso es un seq scan (09); (3) UNION ALL (rápido, permite duplicados de ruta) vs UNION (deduplica, más caro): en árboles honestos, UNION ALL.

6. GROUP BY con criterio: la trampa del grano (revisitada con más profundidad)

La pregunta que decide el diseño de TODO informe: ¿qué ES una fila del resultado? "Ventas por día" (una fila = día), "ventas por evento y día" (una fila = evento×día), "ranking de eventos por mes con su mejor día" (una fila = evento, con una subagregación dentro). El error del grano equivocado: mezclar un COUNT de nivel día con un MAX de nivel fila sin GROUP BY correcto → números que no cuadran con la contabilidad del 08.

-- Por evento: su venta total Y el día de su mejor venta (dos granos en una query)
SELECT e.name,
       SUM(r.total_amount)                          AS total_evento,
       (SELECT SUM(r2.total_amount)
          FROM reservations r2
         WHERE r2.event_id = e.id AND r2.status = 'CONFIRMED'
         GROUP BY DATE(r2.created_at)
         ORDER BY 1 DESC LIMIT 1)                   AS mejor_dia
FROM events e
JOIN reservations r ON r.event_id = e.id AND r.status = 'CONFIRMED'
GROUP BY e.id, e.name;

La alternativa moderna sin subquery: GROUP BY GROUPING SETS ((e.id), (e.id, DATE(r.created_at))) — una sola pasada que produce el grano evento Y el grano evento×día, con GROUPING() para saber qué nivel es cada fila. Útil para el informe del 31; legible solo con comentario.

Autoevaluación (respóndeme en el chat)

1. ¿Cuándo elegirías NOT EXISTS frente a LEFT JOIN... IS NULL y por qué el primero suele ser más claro?
2. ¿Qué diferencia hay entre RANK() y ROW_NUMBER() y qué pasa con los empates en cada uno?
3. ¿Por qué el "join + dos SUM" del punto 3 duplica importes y a qué nivel de grano hay que agregar?
4. ¿Qué aporta FILTER (WHERE...) frente a CASE WHEN dentro de un SUM?
5. En la consulta de ingresos con generate_series: ¿por qué el LEFT JOIN va desde la serie y no desde payment?

Continúa con los ejercicios. Las soluciones solo tras intentarlo.