Lección 34: Mini-proyecto – Analítica de ventas

Construye reportes avanzados: ranking, última venta por cliente y porcentaje del total.

Reto 1: Ranking de clientes por monto total

Solución 
WITH t AS (
  SELECT id_cliente, SUM(total) AS monto_total
  FROM ventas
  GROUP BY id_cliente
)
SELECT t.*,
       DENSE_RANK() OVER (ORDER BY monto_total DESC) AS ranking
FROM t
ORDER BY monto_total DESC;

Reto 2: Última venta por cliente (ROW_NUMBER)

Solución 
WITH x AS (
  SELECT v.*,
         ROW_NUMBER() OVER (PARTITION BY id_cliente ORDER BY fecha DESC) AS rn
  FROM ventas v
)
SELECT *
FROM x
WHERE rn = 1;

Reto 3: Porcentaje del total por venta (SUM OVER)

Solución 
SELECT id_venta, total,
       SUM(total) OVER() AS total_global,
       CAST(100.0 * total / SUM(total) OVER() AS decimal(10,2)) AS porcentaje
FROM ventas;