Definición y Usos:

• Uso de CTEs para simplificar consultas SQL complejas, mejorar la legibilidad y permitir cálculos recursivos.

Ejemplos de Uso:

• Serie de Fibonacci:

  WITH Fibonacci(num, val, previous) AS (
      SELECT 1 AS num, 0 AS val, 1 AS previous
      UNION ALL
      SELECT
          num + 1 AS num,
          previous AS val,
          val + previous AS previous
      FROM Fibonacci
      WHERE num < 200
  )
  SELECT * FROM Fibonacci;
  
  

• Agrupaciones y Filtrados:

  • Ventas totales y promedio por categoría:

    WITH CategorySales AS (
        SELECT P.CategoryID, SUM(OD.Quantity) AS TotalSales
        FROM [Order Details] OD
        JOIN Products P ON OD.ProductID = P.ProductID
        GROUP BY P.CategoryID
    ),
    AvgCategorySales AS (
        SELECT CategoryID, AVG(TotalSales) AS AvgSales
        FROM CategorySales
        GROUP BY CategoryID
    )
    SELECT P.ProductName, P.CategoryID, SUM(OD.Quantity) AS SalesQuantity
    FROM [Order Details] OD
    JOIN Products P ON OD.ProductID = P.ProductID
    GROUP BY P.ProductName, P.CategoryID
    HAVING SUM(OD.Quantity) > (SELECT AvgSales FROM AvgCategorySales WHERE CategoryID = P.CategoryID)
    ORDER BY P.CategoryID, SalesQuantity DESC;
    
    

• Jerarquía de Empleados:

  WITH EmployeeHierarchy AS (
      SELECT EmployeeID, FirstName, LastName, ReportsTo, 0 AS Level
      FROM Employees
      WHERE ReportsTo IS NULL
  
      UNION ALL
  
      SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, eh.Level + 1
      FROM Employees e
      INNER JOIN EmployeeHierarchy eh ON e.ReportsTo = eh.EmployeeID
  )
  SELECT EmployeeID, FirstName, LastName, ReportsTo, Level
  FROM EmployeeHierarchy
  ORDER BY Level, ReportsTo, LastName;
  
  

Promedio y Comparaciones:

• Productos con precios superiores al promedio de su categoría:

  WITH Promedios AS (
      SELECT ROUND(AVG(UnitPrice), 2) AS PromedioCategoria, CategoryID
      FROM Products
      GROUP BY CategoryID
  ), ProductosMayoresAlPromedio AS (
      SELECT P.CategoryID, P.UnitPrice, P.ProductName
      FROM Products P
      INNER JOIN Promedios PR ON PR.CategoryID = P.CategoryID
      WHERE P.UnitPrice > PR.PromedioCategoria
  )
  SELECT *
  FROM ProductosMayoresAlPromedio
  ORDER BY CategoryID, UnitPrice;
  
  

Clientes con Mayores Gastos:

WITH Consulta AS (
    SELECT O.CustomerID, OD.UnitPrice, SUM(OD.Quantity) AS Cantidad
    FROM Orders O
    INNER JOIN [Order Details] OD ON O.OrderID = OD.OrderID
    GROUP BY O.CustomerID, O.OrderID, OD.UnitPrice
), Gastado AS (
    SELECT CustomerID, SUM(UnitPrice * Cantidad) AS GastoTotal
    FROM Consulta
    GROUP BY CustomerID
)
SELECT TOP 5 *
FROM Gastado
ORDER BY GastoTotal DESC;