Hoja de referencia de SQL para analistas de datos

Primeras filas de una tabla

SELECT *
FROM products
LIMIT 10;

Solo las columnas que necesitas

SELECT name, category, unit_price
FROM products;

Una columna calculada con nombre

SELECT name,
       unit_price - unit_cost AS margin,
       ROUND((unit_price - unit_cost) / unit_price * 100, 1) AS margin_pct
FROM products;

Cualquier operación aritmética con NULL da NULL: los productos sin unit_cost tienen un margen NULL.

Valores únicos (o combinaciones)

SELECT DISTINCT category, subcategory
FROM products
ORDER BY category, subcategory;

Igual a un valor

SELECT order_id, order_date, channel
FROM orders
WHERE status = 'refunded';

Varias condiciones, con paréntesis

SELECT name, category, unit_price
FROM products
WHERE category IN ('Decor', 'Kitchen')
  AND (unit_price < 50 OR NOT is_active);

AND se evalúa antes que OR. Pon entre paréntesis cada OR que mezcles con AND.

Un rango de fechas (semiabierto)

SELECT order_id, order_date
FROM orders
WHERE order_date >= DATE '2024-03-01'
  AND order_date <  DATE '2024-04-01';

BETWEEN '2024-03-01' AND '2024-03-31' descarta sin avisar los pedidos hechos después de la medianoche del día 31.

Patrón de texto

SELECT customer_id, email
FROM customers
WHERE email ILIKE '%@gmail.com';

ILIKE ignora mayúsculas y minúsculas (PostgreSQL, DuckDB). En otros motores: LOWER(email) LIKE ...

Filas donde falta un valor

SELECT customer_id, email
FROM customers
WHERE referral_source IS NULL;

= NULL nunca es verdadero. Usa IS NULL / IS NOT NULL.

Reemplazar NULL por una etiqueta

SELECT COALESCE(referral_source, 'unknown') AS source,
       COUNT(*) AS customers
FROM customers
GROUP BY 1;

COUNT(*) cuenta filas, COUNT(col) omite los NULL

SELECT COUNT(*)               AS customers,
       COUNT(referral_source) AS with_source
FROM customers;

Dividir sin error de división entre cero

SELECT channel,
       SUM(amount) / NULLIF(COUNT(*), 0) AS avg_daily_spend
FROM marketing_spend
GROUP BY channel;

Poner los NULL primero al ordenar

SELECT name, unit_cost
FROM products
ORDER BY unit_cost NULLS FIRST;

Lo más caro primero, con un criterio de desempate

SELECT product_id, name, unit_price
FROM products
ORDER BY unit_price DESC, product_id;

Top 10

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 10;

Página 2 de 10 filas

SELECT product_id, name
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

Totales de toda la tabla

SELECT COUNT(*)          AS lines,
       SUM(quantity)     AS units,
       AVG(unit_price)   AS avg_price,
       MIN(unit_price), MAX(unit_price)
FROM order_items;

Una fila por grupo

SELECT channel, status, COUNT(*) AS orders
FROM orders
GROUP BY channel, status
ORDER BY channel, orders DESC;

Conteo de valores distintos por grupo

SELECT channel, COUNT(DISTINCT customer_id) AS buyers
FROM orders
GROUP BY channel;

Filtrar grupos con HAVING

SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 20;

Orden de ejecución: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE filtra filas, HAVING filtra grupos.

Agrupar valores en tramos

SELECT CASE WHEN unit_price < 50  THEN 'under 50'
            WHEN unit_price < 200 THEN '50-199'
            ELSE '200+' END AS band,
       COUNT(*) AS products
FROM products
GROUP BY 1;

CASE se detiene en el primer WHEN verdadero, así que ordena las pruebas de lo más estrecho a lo más amplio.

Una tasa por grupo (agregación condicional)

SELECT channel,
       COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       ROUND(AVG(CASE WHEN status = 'cancelled' THEN 1.0 ELSE 0 END) * 100, 1) AS cancel_pct
FROM orders
GROUP BY channel;

FILTER funciona en PostgreSQL y DuckDB; SUM(CASE WHEN ... THEN 1 ELSE 0 END) funciona en todas partes.

INNER JOIN: ingresos por categoría

SELECT p.category,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100)), 2) AS revenue
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Filas sin coincidencia (anti-join)

SELECT c.customer_id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Conservar los conteos en cero: filtra en ON, no en WHERE

SELECT c.customer_id, COUNT(o.order_id) AS app_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.channel = 'app'
WHERE c.segment = 'business'
GROUP BY c.customer_id;

Si pones o.channel = 'app' en WHERE, los clientes sin pedidos de la app desaparecen: el LEFT JOIN se convierte en un inner join.

Contar después de un JOIN uno a muchos

SELECT o.channel,
       COUNT(*)                   AS lines,
       COUNT(DISTINCT o.order_id) AS orders
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY o.channel;

Tras unir las líneas de pedido, cada pedido aparece una vez por línea (multiplicación de filas). Nunca hagas SUM de una columna a nivel de pedido, como shipping_fee, aquí.

Una tabla unida consigo misma

SELECT e.first_name || ' ' || e.last_name AS employee,
       m.first_name || ' ' || m.last_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id;

Totales mensuales

SELECT DATE_TRUNC('month', order_date) AS month,
       COUNT(*) AS orders
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY 1
ORDER BY 1;

Año, mes y día de la semana

SELECT EXTRACT(year FROM order_date)  AS yr,
       EXTRACT(month FROM order_date) AS mo,
       EXTRACT(dow FROM order_date)   AS weekday,  -- 0 = Sunday
       COUNT(*) AS orders
FROM orders
GROUP BY 1, 2, 3;

Días entre dos fechas

SELECT r.return_id,
       r.returned_on - CAST(o.order_date AS DATE) AS days_to_return
FROM returns AS r
JOIN order_items AS oi ON oi.order_item_id = r.order_item_id
JOIN orders AS o ON o.order_id = oi.order_id;

DATE menos DATE da días completos en PostgreSQL y DuckDB. SQL Server y BigQuery usan DATEDIFF / DATE_DIFF.

Los últimos 30 días de datos

SELECT COUNT(*) AS orders
FROM orders
WHERE order_date >= TIMESTAMP '2025-12-31' - INTERVAL '30 days';

Quitar espacios y corregir mayúsculas

SELECT UPPER(TRIM(state)) AS state, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

Dividir texto por un carácter

SELECT SPLIT_PART(email, '@', 2) AS domain, COUNT(*) AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;

Registros duplicados según una clave limpia

SELECT LOWER(TRIM(email)) AS email_key,
       COUNT(*) AS records,
       MIN(customer_id) AS keep_id
FROM customers
GROUP BY 1
HAVING COUNT(*) > 1;

Unir texto

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

CONCAT(a, b) también funciona y trata NULL como vacío; || devuelve NULL si cualquiera de los dos lados es NULL.

Comparar con un número general

SELECT name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

Filas que tienen coincidencia (EXISTS)

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  JOIN returns AS r ON r.order_item_id = oi.order_item_id
  WHERE o.customer_id = c.customer_id
);

NOT IN (subconsulta) no devuelve ninguna fila si la subconsulta contiene un NULL. NOT EXISTS es seguro.

Ponle nombre a cada paso con WITH

WITH order_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price * (1 - discount_pct / 100)) AS merch
  FROM order_items
  GROUP BY order_id
)
SELECT o.customer_id,
       COUNT(*)              AS orders,
       ROUND(SUM(t.merch), 2) AS lifetime_value,
       ROUND(AVG(t.merch), 2) AS avg_order_value
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.customer_id;

Pre-agregar antes de unir (sin multiplicar filas)

WITH merch AS (
  SELECT order_id, SUM(quantity * unit_price) AS merch
  FROM order_items
  GROUP BY order_id
)
SELECT o.channel,
       SUM(o.shipping_fee) AS shipping,
       ROUND(SUM(m.merch), 2) AS merchandise
FROM orders AS o
LEFT JOIN merch AS m ON m.order_id = o.order_id
GROUP BY o.channel;

Top 3 por grupo

WITH ranked AS (
  SELECT category, name, unit_price,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY unit_price DESC, product_id) AS rn
  FROM products
)
SELECT category, name, unit_price
FROM ranked
WHERE rn <= 3;

No puedes filtrar por una función de ventana en WHERE: primero envuélvela en una CTE o una subconsulta.

Empates: ROW_NUMBER, RANK, DENSE_RANK

SELECT name, unit_price,
       ROW_NUMBER() OVER (ORDER BY unit_price DESC) AS row_num,
       RANK()       OVER (ORDER BY unit_price DESC) AS rnk,       -- 1, 2, 2, 4
       DENSE_RANK() OVER (ORDER BY unit_price DESC) AS dense_rnk  -- 1, 2, 2, 3
FROM products;

Total acumulado

WITH daily AS (
  SELECT CAST(order_date AS DATE) AS day, SUM(shipping_fee) AS fees
  FROM orders
  WHERE order_date >= DATE '2025-01-01'
  GROUP BY 1
)
SELECT day, fees,
       SUM(fees) OVER (ORDER BY day) AS fees_ytd
FROM daily;

Cambio mes a mes (LAG)

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT month, orders,
       LAG(orders) OVER (ORDER BY month) AS prev,
       ROUND((orders - LAG(orders) OVER (ORDER BY month)) * 100.0
             / LAG(orders) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly;

Media móvil de 7 días

WITH daily AS (
  SELECT CAST(order_date AS DATE) AS day, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT day, orders,
       AVG(orders) OVER (ORDER BY day
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily;

ROWS cuenta filas, no días: un día sin pedidos no tiene fila, así que completa primero el calendario si los huecos importan.

Valor promedio del pedido (AOV)

SELECT ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100))
             / COUNT(DISTINCT o.order_id), 2) AS aov
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'completed';

Tasa de conversión por fuente de tráfico

SELECT source,
       COUNT(*) AS sessions,
       ROUND(AVG(CASE WHEN converted THEN 1.0 ELSE 0 END) * 100, 2) AS conversion_pct
FROM web_sessions
GROUP BY source
ORDER BY conversion_pct DESC;

Tasa de devolución por categoría

SELECT p.category,
       ROUND(COUNT(r.return_id) * 100.0 / COUNT(*), 1) AS return_pct
FROM order_items AS oi
JOIN products AS p ON p.product_id = oi.product_id
LEFT JOIN returns AS r ON r.order_item_id = oi.order_item_id
GROUP BY p.category;