Las 5 consultas SQL que todo analista de BI debe conocer

🎯 Situación

Una analista junior se unió al equipo de un cliente con sólidas habilidades en Power BI pero casi nada de SQL. En una semana, estaba bloqueada en tres tareas diferentes: unir dos tablas que Power Query manejaba mal, calcular una comparación mes a mes que DAX complicaba, e identificar clientes sin pedidos en los últimos 90 días.

⚠️ El reto

🔍 Análisis

The 5 patterns:

1. Aggregation with GROUP BY — revenue, count, average per dimension (customer, region, product). The foundation of every BI report.

2. Month-over-month comparison with LAG() — window function that accesses the previous row. Replaces complex DAX time intelligence for many use cases.

3. LEFT JOIN to find missing records — customers with no orders, products never sold, regions with no data. The anti-join pattern that's impossible to express cleanly in Power Query.

4. CTE (WITH clause) — break a complex query into named steps. Makes 30-line queries readable. Every BI analyst who writes SQL regularly should use CTEs by default.

5. CASE WHEN for conditional columns — the SQL equivalent of IF statements. Segment customers, categorize products, flag anomalies — all in a single SELECT.

✓️ Buena práctica

Each pattern as a concrete example:

Pattern 1 — Aggregation:
SELECT region, SUM(revenue) AS total_revenue, COUNT(DISTINCT customer_id) AS customers
FROM orders GROUP BY region ORDER BY total_revenue DESC;

Pattern 2 — Month-over-month with LAG:
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

Pattern 3 — Find missing records (LEFT JOIN):
SELECT c.customer_id, c.name FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
WHERE o.order_id IS NULL;

Pattern 4 — CTE:
WITH monthly AS (SELECT DATE_TRUNC('month', order_date) AS month, SUM(revenue) AS rev FROM orders GROUP BY 1)
SELECT month, rev, LAG(rev) OVER (ORDER BY month) AS prev FROM monthly;

Pattern 5 — CASE WHEN:
SELECT customer_id, total_revenue,
CASE WHEN total_revenue > 100000 THEN 'Tier 1'
     WHEN total_revenue > 50000 THEN 'Tier 2'
     ELSE 'Tier 3' END AS customer_tier
FROM customer_revenue;