Nivel 4 · Ingresos por envío y por mercancía de cada canal

La consulta de Brian es un caso de manual del fan-out del capítulo 5. shipping_fee vive en orders, un valor por pedido. Tras unir con order_items, la tarifa de cada pedido se copia en todas las líneas, y SUM(o.shipping_fee) la suma una vez por línea. Ejecuta el código inicial: el envío sale aproximadamente 1.5 veces demasiado alto en web y app y unas 2.4 veces demasiado alto en marketplace, donde los pedidos que pagan tarifa suelen tener más líneas. La inflación ni siquiera es un factor constante que puedas dividir. Los ingresos por mercancía están bien, porque es un número a nivel de línea sumado a nivel de línea.

La solución: agrega primero el lado muchos

Lleva order_items al grano de orders (una fila por pedido) antes de unir:

WITH line_totals AS (
  SELECT order_id,
         SUM(quantity * unit_price * (1 - discount_pct / 100.0)) AS merchandise
  FROM order_items
  WHERE quantity > 0
  GROUP BY order_id
)
SELECT o.channel,
       COUNT(*) AS orders,
       ROUND(SUM(o.shipping_fee), 2) AS shipping_revenue,
       ROUND(SUM(COALESCE(l.merchandise, 0)), 2) AS merchandise_revenue
FROM orders AS o
LEFT JOIN line_totals AS l ON l.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.channel;

Ahora cada pedido coincide con como máximo una fila de line_totals. El join no puede multiplicar nada, así que ambas sumas son correctas y COUNT(*) vuelve a contar pedidos.

Por qué LEFT JOIN y COALESCE

Un pedido cuyas líneas fueron todas filtradas (por ejemplo, solo cantidades erróneas) no tiene fila en line_totals. Un inner join descartaría ese pedido y su tarifa de envío. El LEFT JOIN lo conserva, y COALESCE(l.merchandise, 0) trata su mercancía faltante como cero. Aquí cero es un valor por defecto verdadero: el pedido no tuvo mercancía válida, así que este es el uso legítimo de COALESCE del capítulo 6, no esconder un valor desconocido.

La receta general

Cuando necesitas medidas de dos tablas con granos distintos:

1. Decide el grano de salida (aquí: canal).
2. Agrega cada tabla “muchos” hasta la clave del join (aquí: por pedido) en su propio CTE.
3. Une los CTE uno a uno y luego agrega al grano de salida.

Con dos tablas hijas, por ejemplo líneas y devoluciones por pedido, la receta importa aún más. Unir ambas tablas en bruto con orders multiplica líneas por devoluciones, y todos los números salen mal. Preagrega cada una por separado y luego haz el join.

Concilia

La comprobación de finanzas es sencilla: SELECT SUM(shipping_fee) FROM orders WHERE status = 'completed' debe ser igual a la suma de shipping_revenue entre los canales. Si lo es, el fan-out ha desaparecido. Incorpora este tipo de verificación cruzada a tu rutina para cualquier número que vaya a finanzas.

Nota de entrevista

Este patrón, “agrega antes de unir”, es una de las señales más claras de experiencia en una entrevista de analista. Si te piden combinar pedidos, artículos y pagos o sesiones, di desde el principio qué grano tiene cada tabla y cómo evitarás el doble conteo. A menudo importa más que la sintaxis final.

Errores comunes

• Sumar el envío tras un join en bruto con order_items. Cada tarifa se suma una vez por línea. Lleva las líneas a una fila por pedido en un CTE y luego haz el join.
• Dividir el total inflado entre el promedio de líneas por pedido. La inflación difiere por canal, así que ningún factor único lo arregla.
• Usar un inner join con los totales de línea. Los pedidos cuyas líneas fueron todas filtradas desaparecen, y también sus tarifas de envío. Usa LEFT JOIN con COALESCE(l.merchandise, 0).
• Unir dos tablas hijas a la vez. Las líneas y las devoluciones unidas juntas a orders se multiplican entre sí. Preagrega cada una en su propio CTE.

En SQL Server

ISNULL(l.merchandise, 0) es la versión de dos argumentos de COALESCE en SQL Server. Una diferencia: ISNULL devuelve el tipo de datos de su primer argumento, COALESCE el tipo más amplio de todos los argumentos, así que prefiere COALESCE cuando los tipos difieren.