SQL para Analistas de Datos: Window Functions, CTEs y Consultas Avanzadas

Las SQL window functions, los CTEs (Common Table Expressions) y los patrones avanzados de consultas constituyen la columna vertebral del SQL analitico. Ya sea durante la preparacion para una entrevista de data analyst o al enfrentar consultas complejas de reporteo, estas tecnicas transforman subconsultas extensas y dificiles de leer en SQL limpio y eficiente.

Fundamentos de las SQL Window Functions y la Clausula OVER

Una window function aplica un calculo sobre una "ventana" definida de filas. La clausula OVER controla cuales filas forman parte de esa ventana y como se ordenan. A diferencia de las funciones de agregacion con GROUP BY, las window functions conservan cada fila individual en el resultado.

La sintaxis general sigue un patron consistente en todos los motores de bases de datos principales: PostgreSQL, MySQL 8+, BigQuery, SQL Server y Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY divide los datos en grupos (similar a GROUP BY, pero sin colapsar las filas). ORDER BY define la secuencia dentro de cada particion. La clausula de marco opcional (ROWS BETWEEN ...) limita la ventana a un rango especifico de filas.

En entrevistas tecnicas para roles de data analyst, es comun que se pida explicar la diferencia entre una funcion de agregacion con GROUP BY y una window function. La respuesta clave: GROUP BY reduce multiples filas a una sola fila por grupo, mientras que una window function calcula un valor para cada fila sin eliminar ninguna del resultado.

Comparacion entre ROW_NUMBER, RANK y DENSE_RANK

Estas tres funciones de ranking parecen similares, pero se comportan de manera diferente cuando existen empates. Elegir la incorrecta es una fuente frecuente de errores en consultas analiticas y una de las sql interview questions mas recurrentes.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER siempre asigna un entero secuencial unico: incluso ante empates, una fila recibe arbitrariamente el numero menor. RANK asigna el mismo numero a los empates pero omite los numeros siguientes (1, 1, 3). DENSE_RANK tambien maneja empates, pero nunca omite numeros (1, 1, 2). Para consultas de tipo top-N donde los duplicados deben compartir posicion, DENSE_RANK suele ser la opcion correcta.

Un caso practico habitual en equipos de analisis de datos: obtener los 3 productos con mayor ingreso por categoria. Si dos productos empatan en el tercer lugar, RANK los incluiria a ambos (y el siguiente seria el puesto 5), mientras que DENSE_RANK los incluiria y el siguiente seria el puesto 4.

LAG y LEAD para Analisis Periodo contra Periodo

LAG y LEAD acceden a datos de filas anteriores o posteriores sin necesidad de self-joins. Estas funciones resultan esenciales para calcular crecimiento periodo contra periodo, detectar tendencias e identificar cambios en metricas clave.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

El segundo argumento de LAG/LEAD especifica el desplazamiento (por defecto es 1). Un tercer argumento opcional proporciona un valor por defecto cuando no existe fila en ese desplazamiento, lo cual resulta util para evitar NULLs en la primera o ultima fila. El NULLIF en el calculo de crecimiento previene errores de division por cero.

Este patron aparece con frecuencia en entrevistas tecnicas. Los entrevistadores esperan que el candidato explique no solo la mecanica de la consulta, sino tambien la interpretacion de negocio: un crecimiento mes a mes negativo sostenido requiere investigacion de causas, mientras que un pico aislado podria deberse a estacionalidad o una campana puntual.

NTILE y Segmentacion por Percentiles

NTILE distribuye las filas en un numero especificado de grupos aproximadamente iguales. Los analistas de datos la utilizan para analisis de cuartiles, scoring por deciles y segmentacion de clientes.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

El cuartil 1 contiene a los clientes con mayor gasto y el cuartil 4 a los de menor gasto. Este patron se mapea directamente a la segmentacion RFM (Recency, Frequency, Monetary) utilizada en analisis de marketing. En la practica, los equipos de crecimiento utilizan esta segmentacion para personalizar comunicaciones: los clientes en el decil 1 reciben ofertas de fidelizacion, mientras que los del decil 10 reciben incentivos de reactivacion.

Common Table Expressions: Reemplazando Subconsultas Anidadas

Los CTEs (clausulas WITH) descomponen consultas complejas en pasos nombrados y legibles. Cada CTE actua como un conjunto de resultados temporal que existe unicamente durante la ejecucion de la consulta. Mas alla de la legibilidad, los CTEs facilitan la depuracion, ya que cada paso puede probarse de forma independiente.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Este enfoque de tres pasos reemplaza lo que de otra forma seria una subconsulta profundamente anidada. Cada CTE tiene una responsabilidad clara: agregacion, ranking y filtrado. Cuando un analista necesita modificar la logica de ranking o agregar un filtro adicional, el cambio se localiza en un solo CTE sin afectar al resto de la consulta.

CTEs Recursivos para Datos Jerarquicos

Los CTEs recursivos resuelven problemas que involucran datos jerarquicos o con estructura de grafo: organigramas, arboles de categorias, listas de materiales y consultas de busqueda de rutas.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

El caso base selecciona los nodos raiz (empleados sin gerente). El caso recursivo se une de vuelta al CTE, construyendo la jerarquia nivel por nivel. La columna management_chain concatena nombres para mostrar la cadena completa de reporte. La mayoria de las bases de datos limitan la profundidad de recursion para prevenir ciclos infinitos: PostgreSQL utiliza un limite por defecto de 100 iteraciones.

Patrones Analiticos Avanzados: Gaps, Islands y Totales Acumulados

El patron gaps-and-islands identifica secuencias consecutivas en los datos: periodos de suscripcion activa, dias consecutivos de inicio de sesion o ciclos de produccion ininterrumpidos. Esta tecnica combina ROW_NUMBER con aritmetica de fechas y es una de las advanced sql queries que mas impresiona a los entrevistadores.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

La idea central es la siguiente: para fechas consecutivas, restar un numero de fila incremental a la fecha siempre produce el mismo valor. Cuando existe un hueco (gap), el valor resultante cambia, creando un nuevo grupo. Esta consulta encuentra todas las rachas de 3 o mas dias consecutivos de inicio de sesion por usuario.

En el contexto de analisis de producto, este patron permite a los equipos identificar a los usuarios mas comprometidos (aquellos con rachas largas) y correlacionar ese comportamiento con metricas de retencion y conversion.

Combinando Window Functions con CASE para Analisis Condicional

Las consultas analiticas del mundo real combinan frecuentemente window functions con expresiones CASE para calcular metricas condicionales dentro de una misma consulta. Este enfoque evita la necesidad de multiples subconsultas o self-joins.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Esta unica consulta calcula un promedio movil de 7 dias, una suma acumulada trimestral que se reinicia cada trimestre, y una bandera de outlier, todo sin subconsultas ni self-joins. El promedio movil suaviza la variabilidad diaria para revelar tendencias reales, mientras que la bandera de outlier senala transacciones que merecen revision individual.

Optimizacion de Rendimiento en Consultas Analiticas

Las window functions y los CTEs son herramientas poderosas, pero pueden convertirse en cuellos de botella en tablas grandes. Varias tecnicas ayudan a mantener el rendimiento a escala.

Primero, indexar las columnas utilizadas en las clausulas PARTITION BY y ORDER BY. Un indice compuesto que coincida con la definicion de la ventana elimina operaciones de ordenamiento costosas.

Segundo, filtrar los datos antes de aplicar las window functions. Colocar condiciones en la clausula WHERE (antes de la evaluacion de window functions) reduce el conjunto de datos que la ventana debe procesar.

Tercero, evitar definiciones de ventana redundantes. Las ventanas nombradas reducen la repeticion y comunican al optimizador que multiples funciones comparten el mismo marco:

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

La clausula WINDOW (soportada en PostgreSQL, MySQL 8+ y BigQuery) define la ventana una sola vez y la reutiliza en multiples funciones. Esto mejora tanto la legibilidad como la potencial optimizacion del plan de consulta por parte del motor de base de datos.

Conclusion

• Las sql window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operan sobre conjuntos de filas sin colapsarlas, a diferencia de las funciones de agregacion con GROUP BY
• Los CTEs descomponen consultas complejas en pasos nombrados y probables, reemplazando subconsultas profundamente anidadas
• Los CTEs recursivos manejan datos jerarquicos como organigramas y arboles de categorias, pero requieren limites de profundidad y columnas de union indexadas
• El patron gaps-and-islands (ROW_NUMBER + aritmetica de fechas) identifica secuencias consecutivas en datos de series temporales
• Las ventanas nombradas (clausula WINDOW) reducen la duplicacion de codigo y pueden mejorar la optimizacion del plan de consulta
• Filtrar datos en clausulas WHERE antes de la evaluacion de window functions mantiene el rendimiento en tablas grandes
• Estos patrones aparecen frecuentemente en entrevistas de data analytics y se aplican directamente al reporteo diario, segmentacion y analisis de tendencias