SQL para Data Engineering

SELECT Avanzado y JOINs

Tipos de JOINs: La Guía Definitiva

-- Pedidos con información del cliente (solo pedidos CON cliente registrado)
SELECT
    o.order_id,
    o.order_date,
    c.name AS customer_name,
    c.email,
    o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
  AND o.order_date >= '2026-01-01'
ORDER BY o.total_amount DESC;

-- TODOS los clientes, con sus pedidos si los tienen
-- Si el cliente no tiene pedidos, aparece con NULLs en las columnas de orders
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id)    AS total_orders,
    SUM(o.total_amount)  AS total_spent,
    MAX(o.order_date)    AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC NULLS LAST;

-- Encontrar clientes SIN pedidos (usando LEFT JOIN + IS NULL)
SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;  -- Los que no tienen match en orders

-- Reconciliación de datos: compara registros de dos sistemas
-- Útil para data quality: ¿qué hay en sistema A pero no en B y viceversa?
SELECT
    a.customer_id AS id_system_a,
    b.customer_id AS id_system_b,
    a.name        AS name_a,
    b.name        AS name_b,
    CASE
        WHEN a.customer_id IS NULL THEN '❌ Solo en sistema B'
        WHEN b.customer_id IS NULL THEN '❌ Solo en sistema A'
        ELSE '✅ En ambos sistemas'
    END AS status
FROM system_a_customers a
FULL OUTER JOIN system_b_customers b 
    ON a.customer_id = b.customer_id
WHERE a.customer_id IS NULL OR b.customer_id IS NULL;  -- Solo los no reconciliados

-- Generar todas las combinaciones de producto × tienda para análisis de gaps
SELECT
    p.product_id,
    p.name AS product_name,
    s.store_id,
    s.city AS store_city
FROM products p
CROSS JOIN stores s
-- Resultado: si hay 100 productos y 50 tiendas → 5,000 filas

-- Uso práctico: generar todas las fechas × segmentos de clientes
-- para detectar qué combinaciones no tienen ventas (gaps en datos)
SELECT d.full_date, seg.segment, COALESCE(s.revenue, 0) AS revenue
FROM dim_date d
CROSS JOIN (VALUES ('Premium'), ('Standard'), ('Basic')) AS seg(segment)
LEFT JOIN fact_sales s ON s.date = d.full_date AND s.segment = seg.segment
WHERE d.year = 2026;

-- Jerarquía de empleados: cada empleado tiene un manager (que también es empleado)
SELECT
    e.employee_id,
    e.name AS employee_name,
    m.name AS manager_name,
    e.department,
    e.salary,
    ROUND((e.salary / m.salary * 100), 1) AS salary_vs_manager_pct
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id  -- Self JOIN
ORDER BY m.name, e.name;

-- Encontrar empleados con mismo salario
SELECT
    a.employee_id, a.name, a.salary,
    b.employee_id AS peer_id, b.name AS peer_name
FROM employees a
JOIN employees b ON a.salary = b.salary AND a.employee_id < b.employee_id
ORDER BY a.salary DESC;

-- Productos que NUNCA se han vendido (Anti Join)
-- Método 1: LEFT JOIN + IS NULL (más eficiente)
SELECT p.product_id, p.name, p.category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

-- Método 2: NOT EXISTS (más expresivo, similar performance)
SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

-- Método 3: NOT IN (cuidado con NULLs!)
SELECT product_id, name
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM order_items WHERE product_id IS NOT NULL
);

GROUP BY y HAVING

GROUP BY es la base del análisis agregado. HAVING filtra grupos (como WHERE, pero después de la agregación).

-- GROUP BY básico con múltiples funciones de agregación
SELECT
    DATE_TRUNC('month', order_date) AS month,
    status,
    COUNT(*)                         AS order_count,
    COUNT(DISTINCT customer_id)      AS unique_customers,
    SUM(total_amount)                AS revenue,
    AVG(total_amount)                AS avg_order_value,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount) AS median_order,
    MIN(total_amount)                AS min_order,
    MAX(total_amount)                AS max_order
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY DATE_TRUNC('month', order_date), status
HAVING COUNT(*) >= 10                    -- Solo grupos con 10+ pedidos
   AND SUM(total_amount) > 10000         -- Y con revenue > $10K
ORDER BY month DESC, revenue DESC;

-- GROUPING SETS: múltiples niveles de agregación en una sola query
SELECT
    category,
    subcategory,
    SUM(revenue) AS total_revenue
FROM sales
GROUP BY GROUPING SETS (
    (category, subcategory),  -- Por categoría y subcategoría
    (category),                -- Solo por categoría
    ()                         -- Total general
)
ORDER BY category NULLS LAST, subcategory NULLS LAST;

-- ROLLUP: jerarquía automática de agregaciones
SELECT
    year, quarter, month,
    SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter, month);
-- Genera: (year,quarter,month), (year,quarter), (year), ()

Subqueries: Inline, Escalares y Correlacionadas

-- 1. SUBQUERY ESCALAR: retorna un solo valor
SELECT
    order_id,
    total_amount,
    (SELECT AVG(total_amount) FROM orders) AS avg_all_orders,
    total_amount - (SELECT AVG(total_amount) FROM orders) AS diff_from_avg
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

-- 2. SUBQUERY EN FROM (Inline View / Derived Table)
SELECT
    customer_segment,
    AVG(customer_revenue) AS avg_revenue_per_customer,
    COUNT(*) AS customer_count
FROM (
    -- Subquery calcula el revenue por cliente
    SELECT
        c.customer_id,
        c.segment AS customer_segment,
        SUM(o.total_amount) AS customer_revenue
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.segment
) AS customer_stats
GROUP BY customer_segment
ORDER BY avg_revenue_per_customer DESC;

-- 3. SUBQUERY CORRELACIONADA: referencia la query externa (costosa!)
-- Para cada empleado, encontrar si tiene el mayor salario en su departamento
SELECT
    e.employee_id,
    e.name,
    e.department,
    e.salary
FROM employees e
WHERE e.salary = (
    SELECT MAX(inner_e.salary)
    FROM employees inner_e
    WHERE inner_e.department = e.department  -- Referencia la query externa
);
-- Nota: Esta query es O(n²) si no se optimiza. Preferir Window Functions.

-- 4. SUBQUERY CON IN / EXISTS
-- EXISTS es más eficiente que IN cuando el subquery retorna muchas filas
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.total_amount > 1000
      AND o.order_date >= '2026-01-01'
);

CTEs — Common Table Expressions

Las CTEs hacen el código SQL más legible y mantenible. Son como "variables" SQL que puedes referenciar múltiples veces en la misma query.

-- CTE BÁSICA: revenue mensual
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount)               AS revenue
    FROM orders
    WHERE status NOT IN ('cancelled', 'returned')
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) /
        LAG(revenue) OVER (ORDER BY month) * 100,
    2) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

-- MÚLTIPLES CTEs ENCADENADAS (una referencia la anterior)
WITH
-- Paso 1: Revenue por cliente
customer_revenue AS (
    SELECT
        customer_id,
        SUM(total_amount)    AS total_revenue,
        COUNT(*)             AS order_count,
        MIN(order_date)      AS first_order,
        MAX(order_date)      AS last_order
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
),
-- Paso 2: Segmentar clientes por revenue
customer_segments AS (
    SELECT
        cr.*,
        c.name,
        c.email,
        CASE
            WHEN cr.total_revenue >= 10000 THEN 'VIP'
            WHEN cr.total_revenue >= 1000  THEN 'High Value'
            WHEN cr.total_revenue >= 100   THEN 'Regular'
            ELSE 'Low Value'
        END AS revenue_segment,
        EXTRACT(DAYS FROM last_order - first_order) AS customer_lifespan_days
    FROM customer_revenue cr
    JOIN customers c ON cr.customer_id = c.customer_id
),
-- Paso 3: Estadísticas por segmento
segment_stats AS (
    SELECT
        revenue_segment,
        COUNT(*)             AS customer_count,
        AVG(total_revenue)   AS avg_revenue,
        SUM(total_revenue)   AS segment_total_revenue
    FROM customer_segments
    GROUP BY revenue_segment
)
-- Query final combina todo
SELECT
    cs.revenue_segment,
    cs.name,
    cs.total_revenue,
    cs.order_count,
    ss.avg_revenue AS segment_avg_revenue,
    ROUND(cs.total_revenue / ss.segment_total_revenue * 100, 2) AS pct_of_segment_revenue
FROM customer_segments cs
JOIN segment_stats ss ON cs.revenue_segment = ss.revenue_segment
ORDER BY cs.total_revenue DESC;

-- CTE RECURSIVA: Jerarquía de empleados (org chart)
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor: CEO (sin manager)
    SELECT
        employee_id, name, manager_id, title, 1 AS level,
        ARRAY[name] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: empleados con manager
    SELECT
        e.employee_id, e.name, e.manager_id, e.title,
        eh.level + 1,
        eh.path || e.name
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
    WHERE eh.level < 10  -- Safety: evitar ciclos infinitos
)
SELECT
    REPEAT('  ', level - 1) || name AS org_chart,
    title,
    level,
    array_to_string(path, ' > ') AS reporting_chain
FROM employee_hierarchy
ORDER BY path;

Window Functions

Las Window Functions son las más poderosas herramientas del SQL analítico. Calculan valores sobre un "ventana" de filas sin colapsar el resultado como GROUP BY.

-- SINTAXIS GENERAL
function_name() OVER (
    [PARTITION BY column1, column2]  -- Divide en grupos (como GROUP BY pero sin colapsar)
    [ORDER BY column3 ASC/DESC]      -- Ordena dentro de cada partición
    [ROWS|RANGE BETWEEN start AND end]  -- Define el frame
)

Categorías de Window Functions

Ejemplos Avanzados de Window Functions

-- Top 3 productos más vendidos por categoría
WITH product_sales AS (
    SELECT
        p.category,
        p.name AS product_name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY p.category, p.name
),
ranked_products AS (
    SELECT
        *,
        RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS row_num
    FROM product_sales
)
SELECT category, product_name, revenue, rnk
FROM ranked_products
WHERE rnk <= 3   -- Top 3 por categoría
ORDER BY category, rnk;

-- Diferencia entre RANK, DENSE_RANK, ROW_NUMBER:
-- Si hay empate en revenue:
-- RANK:       1, 2, 2, 4 (salta el 3)
-- DENSE_RANK: 1, 2, 2, 3 (no salta)
-- ROW_NUMBER: 1, 2, 3, 4 (nunca empate, orden arbitrario en empates)

-- Análisis MoM (Month over Month) de revenue
WITH monthly_stats AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue,
        COUNT(*)          AS orders
    FROM orders
    WHERE status = 'delivered'
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    TO_CHAR(month, 'YYYY-MM') AS period,
    revenue,
    orders,
    LAG(revenue, 1) OVER (ORDER BY month)  AS prev_month_revenue,
    LAG(revenue, 3) OVER (ORDER BY month)  AS revenue_3m_ago,
    LAG(revenue, 12) OVER (ORDER BY month) AS revenue_yoy,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) AS mom_growth_pct,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / LAG(revenue, 12) OVER (ORDER BY month) * 100, 2
    ) AS yoy_growth_pct,
    LEAD(revenue) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_stats
ORDER BY month;

-- Running total (suma acumulada) y Moving Average
SELECT
    order_date,
    total_amount,
    -- Suma acumulada (running total)
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue,
    -- Media móvil de 7 días
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    -- Media móvil de 30 días
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS moving_avg_30d,
    -- % del total del período
    ROUND(
        total_amount / SUM(total_amount) OVER () * 100, 2
    ) AS pct_of_period_total,
    -- % acumulado del período
    ROUND(
        SUM(total_amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)
        / SUM(total_amount) OVER () * 100, 2
    ) AS cumulative_pct
FROM daily_sales
WHERE EXTRACT(YEAR FROM order_date) = 2026
ORDER BY order_date;

-- Segmentar clientes en cuartiles por lifetime value
WITH customer_ltv AS (
    SELECT
        customer_id,
        SUM(total_amount) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    lifetime_value,
    -- División en cuartiles (Q1=bottom 25%, Q4=top 25%)
    NTILE(4) OVER (ORDER BY lifetime_value) AS quartile,
    -- División en deciles
    NTILE(10) OVER (ORDER BY lifetime_value) AS decile,
    -- Percentile rank (0-1)
    ROUND(PERCENT_RANK() OVER (ORDER BY lifetime_value) * 100, 1) AS percentile_rank,
    -- Distribución acumulada
    ROUND(CUME_DIST() OVER (ORDER BY lifetime_value) * 100, 1) AS cumulative_dist
FROM customer_ltv;

-- Resultado para segmentación de marketing:
-- quartile 4, decile 9-10 → VIP campaigns
-- quartile 3 → Upsell campaigns
-- quartile 1-2 → Activation campaigns

Índices: Teoría y Práctica

Los índices son estructuras de datos adicionales que aceleran las búsquedas a costa de mayor storage y tiempo de escritura. Elegir los índices correctos es crucial para el performance.

-- Índice básico B-Tree
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- Índice en múltiples columnas (orden importa!)
-- Útil para queries que filtran por status Y date
CREATE INDEX idx_orders_status_date ON orders(status, order_date DESC);
-- Esta query usa el índice eficientemente:
-- WHERE status = 'delivered' AND order_date >= '2026-01-01'
-- Esta NO (status no es el prefijo de la búsqueda de range):
-- WHERE order_date >= '2026-01-01'  (sin filtro en status)

-- Hash index: solo para comparaciones de igualdad (=)
CREATE INDEX idx_sessions_token ON user_sessions USING HASH (session_token);

-- Eficiente para:
SELECT * FROM user_sessions WHERE session_token = 'abc123xyz';

-- No funciona para:
-- WHERE session_token LIKE 'abc%'  (requiere B-Tree)
-- WHERE session_token > 'abc'      (requiere B-Tree)

-- GIN para búsqueda en columna JSONB
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Query que usa el índice GIN
SELECT * FROM products
WHERE attributes @> '{"color": "red", "size": "L"}';  -- Contiene estas propiedades

-- GIN para Full-Text Search
CREATE INDEX idx_products_fts ON products 
USING GIN (to_tsvector('spanish', name || ' ' || description));

SELECT name, ts_rank(to_tsvector('spanish', name), query) AS rank
FROM products, to_tsquery('spanish', 'laptop & gaming') AS query
WHERE to_tsvector('spanish', name) @@ query
ORDER BY rank DESC;

-- GIN para arrays
CREATE INDEX idx_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'performance'];

-- Caso típico: el 95% de queries son sobre pedidos activos
-- Crear índice solo sobre pedidos NO-cancelados
CREATE INDEX idx_active_orders ON orders(customer_id, order_date)
WHERE status NOT IN ('cancelled', 'returned');

-- Es mucho más pequeño que un índice completo
-- La query DEBE incluir la condición para usar el índice:
SELECT * FROM orders
WHERE customer_id = 1001
  AND status NOT IN ('cancelled', 'returned')  -- ✅ Usa el partial index
  AND order_date > '2026-01-01';

-- Otro ejemplo: solo emails con confirmación pendiente
CREATE INDEX idx_pending_emails ON notification_queue(created_at)
WHERE sent = FALSE AND retry_count < 3;

-- Query frecuente:
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 1001 AND status = 'delivered'
ORDER BY order_date DESC;

-- Covering index: incluye todas las columnas del SELECT
CREATE INDEX idx_orders_covering ON orders(customer_id, status, order_date DESC)
INCLUDE (order_id, total_amount);
-- PostgreSQL puede responder con Index-Only Scan: NO toca la tabla!

-- Verificar con EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 1001 AND status = 'delivered'
ORDER BY order_date DESC;
-- Busca: "Index Only Scan" en el plan

Particionamiento de Tablas

El particionamiento divide físicamente una tabla grande en subtablas más pequeñas (particiones) basándose en un criterio. Mejora drásticamente el performance en tablas de millones/billones de filas.

-- RANGE PARTITIONING: por rango de valores (más común para fechas)
CREATE TABLE orders (
    order_id     BIGSERIAL,
    order_date   DATE NOT NULL,
    customer_id  INTEGER,
    total_amount DECIMAL(10,2),
    status       VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- Crear particiones anuales
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE orders_2026 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- DEFAULT partition (para datos fuera de rango)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- PostgreSQL automáticamente hace "Partition Pruning":
-- Solo busca en particiones relevantes
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';
-- Solo escanea orders_2026, ignora las demás!

-- LIST PARTITIONING: por valores discretos
CREATE TABLE sales_by_region (
    sale_id INTEGER,
    region  VARCHAR(20) NOT NULL,
    amount  DECIMAL(10,2)
) PARTITION BY LIST (region);

CREATE TABLE sales_americas PARTITION OF sales_by_region
    FOR VALUES IN ('US', 'CA', 'MX', 'BR', 'AR');

CREATE TABLE sales_emea PARTITION OF sales_by_region
    FOR VALUES IN ('DE', 'FR', 'UK', 'ES', 'IT');

-- HASH PARTITIONING: distribución uniforme
CREATE TABLE user_events (
    event_id    BIGSERIAL,
    user_id     INTEGER NOT NULL,
    event_type  VARCHAR(50),
    occurred_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- 4 particiones con distribución hash
CREATE TABLE user_events_0 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_events_1 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_events_2 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_events_3 PARTITION OF user_events
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Optimización de Queries

EXPLAIN ANALYZE es tu herramienta más poderosa para entender y optimizar queries. Muestra el plan de ejecución real con costos y tiempos.

-- EXPLAIN ANALYZE: el arma secreta del DBA/DE
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT
    c.name,
    SUM(o.total_amount) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 20;

-- Leer el output de EXPLAIN:
-- Seq Scan   → Escaneo secuencial (malo en tablas grandes)
-- Index Scan → Usa índice (bueno)
-- Index Only Scan → Mejor: no toca la tabla
-- Hash Join  → JOIN eficiente para tablas grandes
-- Nested Loop → JOIN bueno si una tabla es pequeña
-- Sort       → Ordenamiento (caro en disco si no hay memoria)

-- cost=0.00..1234.56 → 0.00 es startup cost, 1234.56 es total cost
-- rows=1000           → Estimado de filas
-- actual time=0.1..25.3 → Tiempo real en ms
-- Buffers: hit=100, read=5 → 100 en cache, 5 leídos de disco

Checklist de Optimización

-- ❌ MALO: Función en columna indexada
WHERE YEAR(order_date) = 2026
-- ✅ BUENO: Range query que usa el índice
WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'

-- ❌ MALO: NOT IN con subquery (lento con NULLs)
WHERE customer_id NOT IN (SELECT customer_id FROM vip_customers)
-- ✅ BUENO: NOT EXISTS (más eficiente)
WHERE NOT EXISTS (SELECT 1 FROM vip_customers v WHERE v.customer_id = o.customer_id)

-- ❌ MALO: COUNT(*) con condición
SELECT COUNT(*) FROM orders WHERE status = 'cancelled'
-- ✅ BUENO: COUNT con filtro (si ya tienes la tabla)
SELECT COUNT(1) FILTER (WHERE status = 'cancelled') FROM orders  -- Una sola pasada

-- ❌ MALO: DISTINCT innecesario (costoso)
SELECT DISTINCT customer_id FROM orders
-- ✅ BUENO: EXISTS cuando solo necesitas saber si existe
SELECT customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.customer_id)

-- ❌ MALO: ORDER BY + LIMIT sin índice
SELECT * FROM events ORDER BY created_at DESC LIMIT 10
-- ✅ BUENO: Asegurar índice en columna de ORDER BY
CREATE INDEX idx_events_created_at ON events(created_at DESC);
SELECT * FROM events ORDER BY created_at DESC LIMIT 10;  -- Index Scan