SQL: La Herramienta Mas Poderosa del Data Engineer

SQL es el lenguaje que todo Data Engineer debe dominar a nivel avanzado. Las tecnicas como Window Functions, CTEs recursivas y optimizacion de queries separan a un junior de un senior. En entrevistas de Mercado Libre, Nubank o Globant, SQL avanzado es la prueba mas comun.

Common Table Expressions (CTEs)

-- CTE para analisis de cohortes LATAM
WITH primera_compra AS (
    SELECT user_id,
        MIN(DATE_TRUNC('month', created_at)) AS cohorte
    FROM orders
    WHERE country IN ('AR', 'MX', 'CO', 'BR', 'CL')
    GROUP BY user_id
),
retencion AS (
    SELECT pc.cohorte,
        EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.created_at), pc.cohorte)) AS meses,
        COUNT(DISTINCT o.user_id) AS activos
    FROM orders o
    JOIN primera_compra pc ON o.user_id = pc.user_id
    GROUP BY 1, 2
)
SELECT cohorte, meses, activos,
    ROUND(100.0 * activos / FIRST_VALUE(activos) OVER (PARTITION BY cohorte ORDER BY meses), 2) AS retencion_pct
FROM retencion ORDER BY cohorte, meses;

Window Functions

-- Top 3 productos por categoria y pais
WITH ventas AS (
    SELECT p.category, p.name, o.country,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY 1, 2, 3
),
ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY country, category ORDER BY revenue DESC) AS ranking
    FROM ventas
)
SELECT * FROM ranked WHERE ranking <= 3;

Con estas tecnicas de SQL avanzado, estas listo para construir transformaciones profesionales.