The Professor
Índice Índice del curso

Módulo 2 · Bases de datos

Lección 07 — SQL avanzado

Joins, subconsultas, CTEs, funciones de ventana y agregaciones sobre PostgreSQL.

Publicada
En esta lección
  1. Ejercicio 1 — CTE de compradores repetidores
  2. Ejercicio 2 — Ranking con ventana
  3. Ejercicio 3 — El duplicado silencioso
  4. Ejercicio 4 — Disponibilidad
  5. Ejercicio 5 — Serie y conversión
  6. Ejercicio 6 — El árbol
  7. Ejercicio 7 — Los dos granos
  8. Resumen del profesor

Ejercicio 1 — CTE de compradores repetidores

sql
WITH confirmadas AS (
    SELECT user_id, COUNT(*) AS n
    FROM reservation WHERE status = 'CONFIRMED' GROUP BY user_id
),
gasto AS (
    SELECT r.user_id, SUM(p.amount) AS total
    FROM reservation r JOIN payment p ON p.reservation_id = r.id
    WHERE r.status = 'CONFIRMED' AND p.status = 'SUCCEEDED'
    GROUP BY r.user_id
)
SELECT u.email, c.n, g.total
FROM confirmadas c
JOIN "user" u ON u.id = c.user_id
JOIN gasto g  ON g.user_id = c.user_id
WHERE c.n >= 3
ORDER BY c.n DESC;
  1. Sin WITH es la misma consulta con subconsultas anidadas: funciona, pero el ojo debe saltar dentro-fuera para entender. CTE gana en review. (La versión con HAVING también es válida para el filtro n>=3.)

Ejercicio 2 — Ranking con ventana

  1. RANK() deja huecos con empates (1,1,3); DENSE_RANK() no (1,1,2). Para "top ventas" da igual; para "posición en una lista paginada" usa ROW_NUMBER().
  2. LAG(SUM(...)) OVER (ORDER BY e.starts_at) y la diferencia con el valor actual; en el primer evento LAG da NULL → envuélvelo con COALESCE(..., 0).
  3. Patrón top-N por grupo:
sql
SELECT * FROM (
  SELECT r.*, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY created_at) AS rn
  FROM reservation r
) t WHERE rn <= 2;

Ejercicio 3 — El duplicado silencioso

  1. Con 1 reserva, 2 ítems y 1 pago: el JOIN produce 2 filas (cada ítem cruza con el pago), así que SUM(p.amount) = 2×el pago. Es el error de grano: agregas a nivel reserva pero el JOIN explotó a nivel ítem.
  2. La versión con CTEs pagos/items agrega cada relación a grano reservation_id antes de unir: importes correctos. Moraleja: agrega primero, cruza después.

Ejercicio 4 — Disponibilidad

  1. Deben coincidir (misma semántica que la subconsulta de la 00b). Si no, revisa los estados filtrados.
  2. LEFT JOIN reservation_item ri... LEFT JOIN reservation r... WHERE r.id IS NULL: válida y a veces más rápida; NOT EXISTS expresa la intención ("no existe reserva activa") y es la que recomendaría en review.
  3. ORDER BY s.sector, s.row, s.number LIMIT 50 — paginación en la BD, no en Python.

Ejercicio 5 — Serie y conversión

  1. Con el pago de ayer, el día aparece con importe; sin él, 0.00. El truco es que la serie es la tabla base y el LEFT JOIN solo decora: así no desaparecen días vacíos.
  2. Expiración: COUNT() FILTER (WHERE r.status = 'CANCELLED' AND r.expires_at < now())::float / NULLIF(COUNT(), 0) sobre reservas; si quieres precisión semántica distingue canceladas-por-usuario de expiradas por timeout (el job de la 29 marcará la causa).

Ejercicio 6 — El árbol

  1. La CTE con guardia:
sql
WITH RECURSIVE org_tree AS (
    SELECT id, parent_id, name, 0 AS depth FROM org WHERE id = :root_id
    UNION ALL
    SELECT o.id, o.parent_id, o.name, t.depth + 1
    FROM org o JOIN org_tree t ON o.parent_id = t.id
    WHERE t.depth < 20                 -- la guardia anti-ciclo
)
...
  1. El ciclo A→B→A sin guardia: el paso recursivo re-encuentra A indefinidamente hasta que Postgres aborta ("statement recursion depth exceeded" en versiones nuevas, o memoria/timeout en las viejas: el desastre es real, no teórico). Con guardia: termina a depth 20 con filas repetidas del ciclo — el dato corrupto necesita fix, la query solo contuvo el daño.
  2. Sin índice en parent_id: cada hop = seq scan de org (el árbol de 10k orgs: 10k filas leídas × 3 hops). Con índice: index scan por hop (~3-5 filas). El costo total baja de decenas de ms a µs: el 09 aplicado al recursivo.

Ejercicio 7 — Los dos granos

  1. Los GROUPING SETS:
sql
SELECT e.name,
       DATE(r.created_at)                        AS dia,
       SUM(r.total_amount)                       AS total,
       GROUPING(e.id, DATE(r.created_at))        AS nivel  -- 0=fila evento×día, 1=fila evento
FROM events e JOIN reservations r ON r.event_id = e.id AND r.status='CONFIRMED'
GROUP BY GROUPING SETS ((e.id), (e.id, DATE(r.created_at)));

El cuadre: la fila nivel evento = la SUMA de sus filas nivel evento×día (el assert del test de la 08). Si no cuadra: hay datos con fecha NULL (GROUPING los agrupa aparte) — el hallazgo típico.

  1. El plan: GROUPING SETS = UNA pasada de agregación (más rápido en datasets grandes: una lectura); el subquery = 2 pasadas pero el plan puede ser más simple con índices selectivos por evento. Con 10k eventos × 90 días: GROUPING SETS gana ~2×; con consultas dirigidas a UN evento, el subquery basta. La regla: informe completo → sets; detalle dirigido → subquery.
  2. El "grano" de cada informe: ventas de la portada = fila por EVENTO; el dashboard del 31 = fila por evento×día; el ledger = fila por TRANSACCIÓN (el más fino, la fuente de todos). Tres granos, una misma verdad contable: el chequeo es que los más finos sumen los más gruesos.

Resumen del profesor

  • CTE para legibilidad; agrega a grano correcto antes de cruzar; ventanas para rankings/series sin colapsar filas.
  • NOT EXISTS expresa intención; generate_series + LEFT JOIN para series completas; FILTER para métricas condicionales.
  • SQL es una habilidad de lectura tanto como de escritura: la query de review se agradece como el código.

Después de la corrección: Lección 08 — Modelado de datos.

^ arriba