Ejercicios – Módulo 4 (SQL Server)

Subconsultas + CTE + Window Functions (OVER). Abre la solución solo después de intentar.

Nivel 1 – Subconsultas (1–7)

1) Productos con precio mayor al promedio

Solución
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

2) Clientes que tienen ventas (IN)

Solución
SELECT nombre
FROM clientes
WHERE id_cliente IN (SELECT id_cliente FROM ventas);

3) Clientes que NO tienen ventas

Solución
SELECT nombre
FROM clientes
WHERE id_cliente NOT IN (SELECT id_cliente FROM ventas);

4) Clientes que tienen ventas (EXISTS)

Solución
SELECT c.nombre
FROM clientes c
WHERE EXISTS (SELECT 1 FROM ventas v WHERE v.id_cliente = c.id_cliente);

5) Clientes con 3 o más ventas (correlacionada)

Solución
SELECT c.nombre
FROM clientes c
WHERE 3 <= (SELECT COUNT(*) FROM ventas v WHERE v.id_cliente = c.id_cliente);

6) Ventas por encima del promedio de ventas

Solución
SELECT *
FROM ventas
WHERE total > (SELECT AVG(total) FROM ventas);

7) Producto más caro (subconsulta)

Solución
SELECT *
FROM productos
WHERE precio = (SELECT MAX(precio) FROM productos);

Nivel 2 – CTE (8–13)

8) CTE: monto total por cliente

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

9) CTE + JOIN: nombre del cliente y su monto_total

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

10) CTE: clientes con monto_total >= 500

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

11) CTE recursivo: números 1..20

Solución
WITH nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 20
)
SELECT n FROM nums
OPTION (MAXRECURSION 100);

12) CTE recursivo: generar días 1..7 (ejemplo)

Solución
WITH dias AS (
  SELECT CAST('2025-01-01' AS date) AS d
  UNION ALL
  SELECT DATEADD(day, 1, d) FROM dias WHERE d < '2025-01-07'
)
SELECT d FROM dias
OPTION (MAXRECURSION 100);

13) CTE: top 5 clientes por monto_total

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

Nivel 3 – Window Functions (14–20)

14) ROW_NUMBER: numerar ventas por cliente (recientes primero)

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

15) Última venta por cliente

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;

16) 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;

17) Total global y porcentaje por venta

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;

18) Acumulado por cliente (por fecha)

Solución
SELECT id_cliente, fecha, total,
       SUM(total) OVER (
         PARTITION BY id_cliente
         ORDER BY fecha
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS acumulado
FROM ventas;

19) Promedio global disponible en cada fila

Solución
SELECT id_venta, total,
       AVG(total) OVER() AS promedio_global
FROM ventas;

20) Top 2 ventas por cliente (ROW_NUMBER)

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