Guía rápida de SQL

Orden de escritura y de ejecución

1. 1FROM / JOIN arma las filas de partida
2. 2WHERE filtra filas (no ve alias ni agregados)
3. 3GROUP BY forma los grupos
4. 4HAVING filtra grupos
5. 5SELECT calcula columnas, alias y ventanas
6. 6DISTINCT quita filas repetidas
7. 7ORDER BY ordena (ya ve los alias)
8. 8LIMIT / OFFSET corta el resultado

SELECT expr AS alias ... ORDER BY alias  -- sí
SELECT expr AS alias ... WHERE alias     -- no

SELECT, filtros y orden

SELECT a, b - c AS neto, d AS "Monto neto"
FROM tabla AS t;

SELECT DISTINCT pais, ciudad FROM clientes;
count(DISTINCT cliente_id)

WHERE a = 1
  AND (b = 2 OR c = 3)
  AND NOT d

WHERE x IN ('a', 'b')
  AND n BETWEEN 10 AND 20   -- incluye extremos
  AND t LIKE 'ab%'  -- ILIKE: sin mayúsculas

ORDER BY rating DESC NULLS LAST, id
LIMIT 10;

LIMIT 10 OFFSET 20          -- simple, lento
WHERE (f, id) < (:f, :id)   -- por clave
ORDER BY f DESC, id DESC LIMIT 10

NULL

WHERE col IS NULL
WHERE (col <> 5 OR col IS NULL)

count(*)  count(col)  avg(col)
coalesce(sum(col), 0)

coalesce(col, 'sin dato')
a / NULLIF(b, 0)

CASE, tipos, fechas y texto

CASE WHEN x < 150 THEN 'corta'
     WHEN x < 240 THEN 'media'
     ELSE 'larga' END

col::numeric   CAST(col AS numeric)
round(a::numeric / b, 2)

date_trunc('month', ts)
extract(year FROM ts)
to_char(ts, 'YYYY-MM')

WHERE ts >= '2025-08-01'
  AND ts <  '2025-09-01'
ts + INTERVAL '1 month'

lower(trim(t))   length(t)
split_part(email, '@', 2)
concat(a, ' ', b)   a || ' ' || b

Agregación, GROUP BY y HAVING

count(*)  count(DISTINCT c)
sum(x)  avg(x)  min(x)  max(x)

SELECT date_trunc('month', ts) AS mes,
       count(*)
FROM t
GROUP BY date_trunc('month', ts);

WHERE status = 'x'        -- filas
GROUP BY g
HAVING count(*) >= 2      -- grupos

count(*) FILTER (WHERE s = 'x')
sum(CASE WHEN s = 'x' THEN 1 ELSE 0 END)

Joins y cardinalidad

FROM a
LEFT JOIN b ON b.a_id = a.id
           AND b.status = 'x'

SELECT count(*), count(DISTINCT o.id)
FROM orders o
JOIN payments p ON p.order_id = o.id

WHERE NOT EXISTS (
  SELECT 1 FROM b WHERE b.a_id = a.id
)

FROM categories h
LEFT JOIN categories p ON p.id = h.parent_id
-- pares: ON a.g = b.g AND a.id < b.id

Subconsultas, CTE y conjuntos

WHERE x > (SELECT avg(x) FROM t)
FROM (SELECT ... GROUP BY ...) AS d
WHERE EXISTS (SELECT 1 FROM h
              WHERE h.p_id = p.id)

WITH paso_1 AS (...),
     paso_2 AS (SELECT ... FROM paso_1)
SELECT ... FROM paso_2;

SELECT c FROM a
UNION ALL        -- UNION quita repetidos
SELECT c FROM b  -- también INTERSECT, EXCEPT

Funciones de ventana

row_number() OVER (PARTITION BY g
                   ORDER BY x DESC, id)
rank(): 1, 1, 3    dense_rank(): 1, 1, 2

avg(x) OVER (PARTITION BY g)
x / sum(x) OVER ()   -- participación

lag(x) OVER (PARTITION BY g ORDER BY fecha)
lead(x, 1, 0) OVER (...)

sum(x) OVER (PARTITION BY g
  ORDER BY fecha, id
  ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW)

Patrones de entrevista

WITH r AS (SELECT *, rank() OVER (
  PARTITION BY g ORDER BY m DESC) AS p
  FROM t)
SELECT * FROM r WHERE p <= 3;

row_number() OVER (PARTITION BY clave
  ORDER BY criterio, id) AS n  -- n = 1
-- PostgreSQL: DISTINCT ON (clave)

row_number() OVER (PARTITION BY entidad
  ORDER BY fecha DESC, id DESC) AS n
-- quedarse con n = 1

avg(x) OVER (ORDER BY dia
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
-- sobre generate_series(ini, fin, '1 day')

round(100.0 * count(*)
  / sum(count(*)) OVER (), 1)
100.0 * a / NULLIF(b, 0)

date_trunc('month', signup_at) AS cohorte
-- LEFT JOIN a la actividad del mes 1
-- activos / tamaño de la cohorte

Rendimiento

WHERE ts >= '2025-08-01'
  AND ts <  '2025-09-01'   -- usa el índice
WHERE date_trunc('month', ts) = ...  -- no

CREATE INDEX ON orders (restaurant_id,
                        placed_at);
EXPLAIN ANALYZE SELECT ...;

WITH r AS (SELECT k, count(*) AS n
  FROM grande GROUP BY k)
SELECT d.nombre, r.n
FROM r JOIN dim d ON d.id = r.k;