Nivel 2 · Verifica y compara pedidos con cupón en SQL

Qué estás verificando y por qué

El cupón vive en dos lugares: el código en orders.coupon_code y el descuento en cada línea de order_items.discount_pct. Todos los números de este caso se construyen a partir del segundo, así que primero demuestra que concuerda con el primero. Una consulta agrupada lo hace:

SELECT COALESCE(o.coupon_code, 'none') AS coupon,
       MIN(i.discount_pct) AS min_discount,
       MAX(i.discount_pct) AS max_discount
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id
GROUP BY coupon;

Si el mínimo y el máximo de un código son iguales, todas sus líneas llevan el mismo descuento. Otras tres cosas que buscar en el mismo resultado: un código escrito de dos maneras (welcome10 junto a WELCOME10), pedidos sin código que aun así llevan descuento, y cuántos pedidos no tienen ningún código. COALESCE importa aquí: sin él, el grupo de NULL es fácil de filtrar por accidente.

Cuánto costó cada cupón

El paso 2 resume primero las líneas en una fila por pedido, en una CTE, porque un cupón es una decisión a nivel de pedido: el valor medio del pedido debe dividirse entre pedidos, no entre líneas. A partir de ahí la hoja de costos es una agregación sencilla:

• revenue: lo que pagaron los clientes, después del descuento;
• aov: ingresos por pedido;
• discount_cost: valor antes del descuento menos valor después, el dinero regalado;
• revenue_share_pct: la porción de cada código en el ingreso neto total, dividida entre una subconsulta escalar (SELECT SUM(net) FROM order_totals) para que el denominador sea el mismo total en todas las filas.

Comparar lo comparable: los mismos días

Cada pedido con cupón promedia menos que un pedido sin código. Suena condenatorio, pero los códigos de temporada corren en semanas concretas, y las cestas de Black Friday son más pequeñas en general. La comparación justa pone cada cupón junto a los pedidos hechos los mismos días sin código.

La segunda CTE, coupon_days, lista los días en que se usó cada código de temporada. Al unirla de vuelta a order_totals por día, obtienes todos los pedidos hechos esos días, una vez por cupón. La agregación condicional los separa:

AVG(CASE WHEN t.coupon = d.coupon THEN t.net END) AS coupon_aov,
AVG(CASE WHEN t.coupon = 'none'   THEN t.net END) AS no_coupon_aov

WELCOME10 y FREESHIP se dejan fuera de este paso porque corren todos los días: para ellos, “los mismos días” significa todo el año.

Cómo leer el resultado

coupon	coupon_basket	coupon_aov	no_coupon_aov
BFCM25	536.54	402.41	483.06
HOLIDAY20	482.84	386.27	479.56
SPRING15	634.59	539.40	574.96
SUMMER15	671.75	570.98	658.07

La cesta antes del descuento es más o menos igual, o algo superior, a la de un pedido normal esos días. Así que los códigos no hicieron que la gente comprara mucho más; sobre todo pagaron menos por una cesta similar.

Trampas

• Un pedido con cupón contado una vez por línea infla orders; cuenta en la CTE a nivel de pedido.
• Filtrar quantity > 0 importa: tres líneas con cupón tienen cantidad cero o negativa.
• El costo de FREESHIP es el envío, que no está en estos totales. Menciónalo en lugar de reportar $0.