Nivel 2 · Construye la revisión mensual en SQL

Qué estás verificando y por qué

Un mes está listo para informarse solo cuando sus pedidos se han asentado. Los pedidos llegan como pending y pasan a completed, cancelled o refunded en los días siguientes. Contar estados por mes muestra dónde están los pedidos abiertos, con el mismo patrón de conteo condicional que cualquier pivote:

SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending

Noviembre tiene 2 pedidos pendientes de 715; diciembre tiene 94 de 777. Esa es la evidencia detrás de informar de noviembre.

Una CTE por fuente

Los KPI vienen de tres tablas con tres granularidades: líneas de pedido, devoluciones y sesiones web. El patrón seguro es llevar cada una a una fila por mes en su propia CTE y luego unir las CTE por mes:

sales   AS (... FROM lines ... GROUP BY month),
refunds AS (... FROM returns GROUP BY month),
web     AS (... FROM web_sessions GROUP BY month)

Los compradores nuevos necesitan un paso más. “Nuevo” significa que el primer pedido completado del cliente cae en el mes, así que una CTE firsts encuentra MIN(order_date) por cliente, y la CTE sales cuenta los compradores cuyo mes del primer pedido es igual al mes del pedido. Los compradores recurrentes son el resto.

Cómo leer el resultado

month	net_revenue	orders	avg_order_value	buyers	web_conversion_pct
2024-11	319,297.22	564	566.13	357	3.50
2025-10	174,599.43	333	524.32	261	2.33
2025-11	292,364.40	651	449.10	442	4.45

Frente a octubre todo sube, como cada noviembre. Frente al año pasado, el volumen creció y los ingresos cayeron, así que la pregunta pasa a ser el valor medio del pedido. El paso 3 la responde: siete pedidos voluminosos el noviembre pasado, uno este noviembre.

Explicar el cambio

El paso 3 trabaja a nivel de pedido, porque el tamaño de un pedido solo se conoce una vez que sus líneas se han sumado. Una CTE suma cada pedido, la consulta externa lo etiqueta con CASE WHEN y agrupa los pedidos por mes y banda. El resultado divide cada noviembre en unos pocos pedidos grandes y muchos regulares, que es el desglose que convierte “los ingresos cayeron” en una razón sobre la que la dirección puede actuar.

Trampas

• strftime sobre una marca de tiempo funciona igual que sobre una fecha, así que returned_on y order_date dan las mismas etiquetas 'YYYY-MM' y las CTE se unen limpiamente.
• COUNT(DISTINCT customer_id) por mes cuenta compradores; sumar compradores entre meses cuenta dos veces a las personas que compraron en ambos.
• Etiquetar el tamaño del pedido en líneas en lugar de pedidos coloca los pedidos grandes en la banda equivocada.