Lección 25: Mini-proyecto – Reportes de ventas

Construye reportes reales usando JOIN + GROUP BY + HAVING (SQL Server).

Reto 1: Top 5 clientes por monto total

Solución 
SELECT TOP 5 c.nombre AS cliente, SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
ORDER BY monto_total DESC;

Reto 2: Clientes sin ventas (LEFT JOIN)

Solución 
SELECT c.id_cliente, c.nombre
FROM clientes c
LEFT JOIN ventas v ON v.id_cliente = c.id_cliente
WHERE v.id_venta IS NULL;

Reto 3: Ventas por cliente (cantidad de ventas y monto)

Solución 
SELECT c.nombre AS cliente,
       COUNT(v.id_venta) AS total_ventas,
       SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
ORDER BY monto_total DESC;

Reto 4: Solo clientes con monto_total >= 500 (HAVING)

Solución 
SELECT c.nombre AS cliente, SUM(v.total) AS monto_total
FROM clientes c
INNER JOIN ventas v ON v.id_cliente = c.id_cliente
GROUP BY c.nombre
HAVING SUM(v.total) >= 500
ORDER BY monto_total DESC;