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 como la escribe la BD
  5. Ejercicio 5 — Serie de ingresos y conversión
  6. Ejercicio 6 — El árbol de organizaciones (CTE recursiva)
  7. Ejercicio 7 — El informe de dos granos
  8. Entrega

Sobre tu PostgreSQL de TicketFlow (psql o Django shell con connection.cursor()). No mires solutions.md hasta entregar.

Preparación: datos de prueba mínimos en shell de Django (o los que ya tengas de la 00b).

Ejercicio 1 — CTE de compradores repetidores

  1. Con un CTE, lista los usuarios con 3 o más reservas confirmadas históricas y su número.
  2. Reescríbelo con subconsulta en el FROM sin WITH: ¿cuál versión leerías mejor en una review?
  3. Añade el importe total gastado por cada uno (segunda CTE + JOIN).

Ejercicio 2 — Ranking con ventana

  1. Ranking de eventos por ingresos (RANK()). ¿Qué pasa si dos empatan?
  2. Añade una columna con el ingreso del evento anterior (LAG()) y la diferencia.
  3. ROW_NUMBER() sobre (event_id, created_at) de las reservas: numera las reservas de cada evento por antigüedad y quédate con las 2 primeras de cada evento (patrón top-N por grupo).

Ejercicio 3 — El duplicado silencioso

  1. Escribe la consulta "MAL" del punto 3 de la lección sobre tu BD (o simúlala con pocos datos) y demuestra con un caso (1 reserva, 2 items, 1 pago) que SUM(p.amount) sale multiplicado.
  2. Arréglalo con CTEs separadas y comprueba el importe correcto.

Ejercicio 4 — Disponibilidad como la escribe la BD

  1. Ejecuta la consulta de disponibilidad (punto 4.1 de la lección) y compara el resultado con tu available_seats() de Python de la 00b. ¿Coinciden?
  2. Reescríbela con LEFT JOIN + IS NULL en vez de NOT EXISTS. ¿Cuál te parece más clara?
  3. Añade el sector y el orden por fila/número, y limita a los primeros 50 (paginación SQL).

Ejercicio 5 — Serie de ingresos y conversión

  1. Ejecuta la consulta de ingresos de 30 días con generate_series e inserta manualmente un pago "de ayer" para ver el día con valor distinto de 0.
  2. Consulta de conversión por evento con FILTER. Añade la de "tasa de expiración" (reservas que expiraron / creadas).

Ejercicio 6 — El árbol de organizaciones (CTE recursiva)

  1. Escribe la CTE recursiva del árbol de organizaciones (§5) con la guardia anti-ciclo (columna depth, WHERE depth < 20). Verifica: un árbol de 3 niveles devuelve ventas por cada nodo, la raíz incluida.
  2. Rompe el dato: inserta un ciclo (A → B → A). Corre SIN guardia (¿la query cuelga o explota?) y con guardia (¿termina?). Documenta la evidencia del desastre y del remedio.
  3. El índice del paso recursivo: EXPLAIN la query sin y con índice en parent_id (09). ¿Cuál es el costo de cada hop?

Ejercicio 7 — El informe de dos granos

  1. Reescribe el informe de ventas por evento con GROUPING SETS ((evento), (evento, día)) y el GROUPING() para etiquetar cada fila con su nivel. Verifica que el total del grano evento coincide con la suma del grano evento×día (el chequeo de cuadre contable, 08).
  2. La query naïf equivalente con subquery (la del §6): compara planes (EXPLAIN ANALYZE) y tiempos. ¿Cuándo compensa cada una?
  3. Documenta en 5 líneas qué fila ES cada informe de tu proyecto (el "grano") — la pregunta que este módulo deja instalada.

Entrega

Pega consultas y salidas recortadas. Al corregir cerramos la 07; después Lección 08 — Modelado de datos.

^ arriba