SQL · Lección 10 de 15

CTEs con WITH

Las Common Table Expressions (CTEs) te permiten definir "tablas temporales" con nombre al inicio de tu consulta usando WITH. Son la alternativa moderna y legible a las subconsultas anidadas. En equipos profesionales, las CTEs son el estándar.

80–100 min Prerrequisitos: SQL 09

Concepto teórico

¿Qué es una CTE?

Una CTE es un SELECT con nombre que definís al inicio con WITH y después usás como si fuera una tabla:

WITH resumen_ciudad AS (
    SELECT ciudad, SUM(saldo) AS total, COUNT(*) AS cantidad
    FROM clientes
    GROUP BY ciudad
)
SELECT ciudad, total, cantidad
FROM resumen_ciudad
WHERE total > 300000;

CTE vs Subconsulta: hacen lo mismo, pero las CTEs son mucho más legibles. En vez de anidar paréntesis, definís cada paso con un nombre descriptivo arriba y los usás abajo. Es como definir funciones en Python antes de usarlas.

Múltiples CTEs

Podés definir varias CTEs separadas por coma, y cada una puede referenciar las anteriores:

WITH
saldos AS (
    SELECT cliente_id, SUM(saldo) AS total
    FROM cuentas GROUP BY cliente_id
),
clasificados AS (
    SELECT c.nombre, s.total,
           CASE WHEN s.total > 500000 THEN 'Alto' ELSE 'Normal' END AS segmento
    FROM clientes c JOIN saldos s ON c.id = s.cliente_id
)
SELECT segmento, COUNT(*), AVG(total)
FROM clasificados
GROUP BY segmento;

En el trabajo: las CTEs son el estándar en Databricks, BigQuery y cualquier motor moderno. Cuando escribís una consulta de 50+ líneas con 3 subconsultas anidadas, nadie la entiende. Con CTEs, cada paso tiene un nombre y se lee como un pipeline: datos_crudos → limpieza → agrupación → resultado.

Buenas prácticas con CTEs: (1) Nombralas descriptivamente: saldos_por_cliente, no tmp1. (2) Cada CTE hace UNA operación. (3) La consulta final al fondo es simple y legible.

Ejemplos explicados paso a paso

Ejemplo 1: CTE básica — reemplazar subconsulta en FROM

ejemplo_01_cte.sqlSQL

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',150000),(2,'López','Córdoba',320000),
(3,'Pérez','Buenos Aires',890000),(4,'Sosa','Rosario',45000),(5,'Luna','Buenos Aires',210000);

-- CTE: primero agrupar, después filtrar
WITH resumen AS (
    SELECT ciudad, SUM(saldo) AS total, COUNT(*) AS cantidad, ROUND(AVG(saldo), 0) AS promedio
    FROM clientes GROUP BY ciudad
)
SELECT ciudad, total, cantidad, promedio
FROM resumen
WHERE total > 200000
ORDER BY total DESC;

Hacé clic en ▶ Ejecutar

Ejemplo 2: Múltiples CTEs — pipeline de datos

ejemplo_02_multi.sqlSQL

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, score INTEGER);
CREATE TABLE cuentas (id INTEGER, cliente_id INTEGER, tipo TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',720),(2,'López','Córdoba',680),
(3,'Pérez','Buenos Aires',810),(4,'Sosa','Rosario',590);
INSERT INTO cuentas VALUES (101,1,'CA$',150000),(102,1,'PF',500000),
(103,2,'CA$',320000),(104,3,'CA$',890000);

-- Pipeline con múltiples CTEs
WITH
saldos_cliente AS (
    SELECT cliente_id, SUM(saldo) AS saldo_total, COUNT(*) AS num_cuentas
    FROM cuentas GROUP BY cliente_id
),
perfil_completo AS (
    SELECT c.nombre, c.ciudad, c.score, s.saldo_total, s.num_cuentas,
           CASE WHEN c.score >= 700 THEN 'Premium' ELSE 'Estándar' END AS categoria
    FROM clientes c
    INNER JOIN saldos_cliente s ON c.id = s.cliente_id
)
SELECT * FROM perfil_completo ORDER BY saldo_total DESC;

Hacé clic en ▶ Ejecutar

Ejemplo 3: CTE para comparar con promedios

ejemplo_03_promedios.sqlSQL

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',150000),(2,'López','Córdoba',320000),
(3,'Pérez','Buenos Aires',890000),(4,'Sosa','Rosario',45000),(5,'Luna','Buenos Aires',210000);

WITH
stats AS (
    SELECT ROUND(AVG(saldo), 0) AS promedio, SUM(saldo) AS total FROM clientes
)
SELECT c.nombre, c.saldo, s.promedio,
       c.saldo - s.promedio AS diferencia,
       ROUND(c.saldo * 100.0 / s.total, 1) AS pct_total,
       CASE WHEN c.saldo > s.promedio THEN '↑ encima' ELSE '↓ debajo' END AS posicion
FROM clientes c, stats s
ORDER BY c.saldo DESC;

Hacé clic en ▶ Ejecutar

Ejemplo 4: CTE para reporte gerencial

ejemplo_04_reporte.sqlSQL

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, score INTEGER);
CREATE TABLE cuentas (id INTEGER, cliente_id INTEGER, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',720),(2,'López','Córdoba',680),
(3,'Pérez','Buenos Aires',810),(4,'Fernández','Córdoba',645),(5,'Sosa','Rosario',590);
INSERT INTO cuentas VALUES (101,1,150000),(102,2,320000),(103,3,890000),(104,4,72000);

WITH
datos AS (
    SELECT c.ciudad, c.score, COALESCE(cu.saldo, 0) AS saldo
    FROM clientes c LEFT JOIN cuentas cu ON c.id = cu.cliente_id
),
por_ciudad AS (
    SELECT ciudad, COUNT(*) AS clientes, SUM(saldo) AS total,
           ROUND(AVG(score), 0) AS score_prom,
           SUM(CASE WHEN score >= 700 THEN 1 ELSE 0 END) AS premium
    FROM datos GROUP BY ciudad
)
SELECT *, ROUND(premium * 100.0 / clientes, 0) AS pct_premium
FROM por_ciudad ORDER BY total DESC;

Hacé clic en ▶ Ejecutar

Ejemplo 5: CTE recursiva — generar secuencia de meses

ejemplo_05_recursiva.sqlSQL

-- CTE recursiva: generar una secuencia de números
WITH RECURSIVE meses(n) AS (
    SELECT 1               -- caso base
    UNION ALL
    SELECT n + 1 FROM meses WHERE n < 12  -- paso recursivo
)
SELECT n AS mes,
       'Mes ' || n AS nombre,
       CASE WHEN n <= 3 THEN 'Q1' WHEN n <= 6 THEN 'Q2'
            WHEN n <= 9 THEN 'Q3' ELSE 'Q4' END AS trimestre
FROM meses;

Hacé clic en ▶ Ejecutar

Referencia rápida

Patrón	Sintaxis
CTE simple	`WITH nombre AS (SELECT ...) SELECT ... FROM nombre`
Múltiples CTEs	`WITH a AS (...), b AS (...) SELECT ... FROM b`
CTE recursiva	`WITH RECURSIVE n AS (base UNION ALL paso) SELECT ...`
CTE + JOIN	Usá la CTE como cualquier tabla en un JOIN

Ejercicios

Nivel 1 · Básico

Ejercicio 1: CTE simple

Creá una CTE que agrupe por ciudad y después filtrá ciudades con total > $300K. Debe incluir Buenos Aires.

ejercicio_01.sqlDebe incluir "Buenos Aires"

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',150000),(2,'López','Córdoba',320000),
(3,'Pérez','Buenos Aires',890000),(4,'Sosa','Rosario',45000);

-- CTE + filtro:

Hacé clic en ▶ Ejecutar

Nivel 1 · Básico

Ejercicio 2: CTE con promedio

Usá una CTE para calcular el promedio y después mostrá clientes por encima de ese promedio. Debe incluir Pérez.

ejercicio_02.sqlDebe incluir "Pérez"

CREATE TABLE clientes (id INTEGER, nombre TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García',150000),(2,'López',320000),(3,'Pérez',890000),(4,'Sosa',45000);

-- CTE con promedio:

Hacé clic en ▶ Ejecutar

Nivel 1 · Básico

Ejercicio 3: CTE con conteo

CTE que cuente clientes por ciudad y muestre solo las que tengan 2+. Debe incluir 2.

ejercicio_03.sqlDebe incluir "2"

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT);
INSERT INTO clientes VALUES (1,'García','Buenos Aires'),(2,'López','Córdoba'),
(3,'Pérez','Buenos Aires'),(4,'Fernández','Córdoba'),(5,'Sosa','Rosario');

-- CTE con conteo:

Hacé clic en ▶ Ejecutar

Nivel 2 · Intermedio

Ejercicio 4: Múltiples CTEs

CTE 1: saldo total por cliente (JOIN cuentas). CTE 2: clasificar en Alto/Normal. Consulta final: listar. Debe incluir Alto.

ejercicio_04.sqlDebe incluir "Alto"

CREATE TABLE clientes (id INTEGER PRIMARY KEY, nombre TEXT);
CREATE TABLE cuentas (id INTEGER, cliente_id INTEGER, saldo REAL);
INSERT INTO clientes VALUES (1,'García'),(2,'López'),(3,'Pérez');
INSERT INTO cuentas VALUES (101,1,150000),(102,1,500000),(103,2,320000),(104,3,890000);

-- Dos CTEs encadenadas:

Hacé clic en ▶ Ejecutar

Nivel 2 · Intermedio

Ejercicio 5: CTE + % del total

CTE con total general. Consulta principal muestra cada cliente con su %. Debe incluir pct.

ejercicio_05.sqlDebe incluir "pct"

CREATE TABLE clientes (id INTEGER, nombre TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García',150000),(2,'López',320000),(3,'Pérez',890000);

-- CTE con total + porcentaje:

Hacé clic en ▶ Ejecutar

Nivel 2 · Intermedio

Ejercicio 6: CTE recursiva — secuencia

Generá una secuencia del 1 al 6 representando cuotas de un préstamo. Debe incluir Cuota.

ejercicio_06.sqlDebe incluir "Cuota"

-- CTE recursiva para cuotas:

Hacé clic en ▶ Ejecutar

Nivel 3 · Avanzado

Ejercicio 7: Pipeline de 3 CTEs

CTE1: saldos por cliente. CTE2: promedio general. CTE3: comparación. Final: los que superan el promedio. Debe incluir encima.

ejercicio_07.sqlDebe incluir "encima"

CREATE TABLE clientes (id INTEGER PRIMARY KEY, nombre TEXT);
CREATE TABLE cuentas (id INTEGER, cliente_id INTEGER, saldo REAL);
INSERT INTO clientes VALUES (1,'García'),(2,'López'),(3,'Pérez');
INSERT INTO cuentas VALUES (101,1,650000),(102,2,320000),(103,3,890000);

-- 3 CTEs:

Hacé clic en ▶ Ejecutar

Nivel 3 · Avanzado

Ejercicio 8: CTE + LEFT JOIN para penetración

CTE con clientes y cuentas. Mostrar penetración por ciudad. Debe incluir penetracion.

ejercicio_08.sqlDebe incluir "penetracion"

CREATE TABLE clientes (id INTEGER PRIMARY KEY, nombre TEXT, ciudad TEXT);
CREATE TABLE cuentas (id INTEGER, cliente_id INTEGER);
INSERT INTO clientes VALUES (1,'García','Buenos Aires'),(2,'López','Córdoba'),
(3,'Pérez','Buenos Aires'),(4,'Sosa','Buenos Aires');
INSERT INTO cuentas VALUES (101,1),(102,2),(103,3);

-- CTE + penetración:

Hacé clic en ▶ Ejecutar

Nivel 3 · Avanzado

Ejercicio 9: CTE para ranking

CTE que ordene clientes por saldo y muestre top 3. Debe incluir ranking.

ejercicio_09.sqlDebe incluir "ranking"

CREATE TABLE clientes (id INTEGER, nombre TEXT, saldo REAL);
INSERT INTO clientes VALUES (1,'García',150000),(2,'López',320000),
(3,'Pérez',890000),(4,'Sosa',45000),(5,'Luna',210000);

-- CTE con ranking (simulado con subconsulta correlacionada):

Hacé clic en ▶ Ejecutar

Nivel 4 · Desafío

Ejercicio 10: Dashboard ejecutivo completo con CTEs

Construí un dashboard con 3 CTEs: base de datos completa, estadísticas por ciudad, y clasificación. Mostrá el resumen final con totales y porcentajes. Debe incluir DASHBOARD.

ejercicio_10.sqlDebe incluir "DASHBOARD"

CREATE TABLE clientes (id INTEGER, nombre TEXT, ciudad TEXT, score INTEGER, saldo REAL);
INSERT INTO clientes VALUES (1,'García','Buenos Aires',720,150000),(2,'López','Córdoba',680,320000),
(3,'Pérez','Buenos Aires',810,890000),(4,'Sosa','Rosario',590,45000),(5,'Luna','Buenos Aires',700,210000);

-- DASHBOARD con CTEs:
SELECT 'DASHBOARD' AS titulo;

Hacé clic en ▶ Ejecutar

Resumen y conexión

WITH nombre AS (SELECT ...) define una CTE — tabla temporal con nombre.
Múltiples CTEs: WITH a AS (...), b AS (...) — cada una puede usar las anteriores.
Las CTEs son más legibles que subconsultas anidadas — estándar en equipos profesionales.
WITH RECURSIVE genera secuencias y recorre estructuras jerárquicas.
CTE + JOIN + GROUP BY + CASE WHEN = reporte gerencial completo y mantenible.

En la siguiente lección (11 · Window Functions) vas a aprender ROW_NUMBER, RANK, LAG/LEAD y funciones de ventana — las herramientas más avanzadas de SQL para análisis.

Hecho con ❤️ por Rodri Gonzalez