Nivel 10 · Desafío final: cuadro de mando por categoría

Este Desafío final encadena varios joins. El truco es llevar la cuenta del grano en cada paso y saber qué joins pueden cambiar el conteo de filas.

Paso 1: tres tablas, un grano

Los ingresos viven en las líneas de pedido, así que order_items es la base. Cada línea tiene exactamente un pedido y un producto, así que unir orders y products añade columnas sin añadir filas. Es un join seguro: muchos a uno. Filtra por o.status e i.quantity, y luego agrupa por categoría.

Paso 2: tasa de devolución con un LEFT JOIN

Las devoluciones son opcionales: la mayoría de las líneas nunca se devuelven. Un LEFT JOIN de las líneas a returns conserva todas las líneas vendidas y rellena las columnas de devolución con NULL cuando no hay ninguna:

LEFT JOIN returns AS r ON r.order_item_id = i.order_item_id

Entonces COUNT(*) cuenta todas las líneas y COUNT(r.return_id) cuenta solo las que tienen una devolución. Como cada línea tiene como mucho una devolución en este conjunto de datos, este join no genera fan-out. En un sistema real donde una línea pudiera devolverse en dos paquetes, contarías DISTINCT i.order_item_id entre las líneas devueltas, o preagregarías las devoluciones por línea primero. Comprueba siempre: SELECT order_item_id, COUNT(*) FROM returns GROUP BY 1 HAVING COUNT(*) > 1 te lo dice en un segundo.

Los filtros sobre o.status e i.quantity están en las tablas base, no en returns, así que dejarlos en WHERE es correcto. Un filtro como r.reason = 'damaged' en WHERE convertiría el LEFT JOIN en uno interno, como viste hace dos niveles. Ese pertenecería a ON.

Una tasa de devolución es aquí una tasa de líneas. Megan podría querer también una tasa de unidades o de ingresos. Di cuál calculaste en tu respuesta.

Paso 3: mejores clientes con nombres

Agrupa por customer_id y también por las columnas de nombre que seleccionas. Agrupar solo por nombre fusionaría a dos clientes distintos que comparten un nombre, y Harbor & Pine tiene algunos registros de clientes duplicados, que desenredarás en el capítulo 7. Agrupa siempre por la clave y añade las columnas descriptivas a su lado.

Comprobación final

Suma los ingresos del paso 1 entre las categorías y compáralos con una consulta al estilo del paso 3 sin el LIMIT y sumada sobre todos los clientes. Ambos deben coincidir exactamente: son las mismas líneas agrupadas de dos formas. Si no coinciden, un join descartó o duplicó algo. Ese hábito de conciliación es lo que hace que la gente confíe en tus números.

Errores comunes

• Filtrar returns en WHERE. WHERE r.reason = 'damaged' descarta toda línea que no se devolvió, así que la tasa de devolución pasa a ser 100 %. Ponlo en el join: LEFT JOIN returns AS r ON r.order_item_id = i.order_item_id AND r.reason = 'damaged'.
• Contar líneas devueltas con COUNT(*). Tras un LEFT JOIN eso cuenta todas las líneas. Usa COUNT(r.return_id), que omite los NULL de las líneas sin devolución.
• Agrupar clientes solo por nombre. Dos clientes distintos que comparten un nombre se fusionan en uno. Agrupa por c.customer_id y añade las columnas de nombre a su lado.
• Usar un inner join con returns. Conserva solo las líneas devueltas, así que el denominador se reduce a las propias devoluciones.

En SQL Server

ORDER BY net_revenue DESC, c.customer_id seguido de LIMIT 5 pasa a ser SELECT TOP 5 ... con el mismo ORDER BY. TOP 5 WITH TIES conserva todas las filas empatadas con la quinta, y OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY es la forma en SQL estándar.