Cacher is the code snippet organizer for pro developers

-- Example 1
-- CTE Name and Column List
WITH Sales_CTE (SalesPersonID,SalesOrderID, SalesYear)
AS
-- CTE Query
(
    SELECT SalesPersonID,
           SalesOrderID,
           YEAR(OrderDate) As SalesYear
    FROM Sales.SalesOrderHeader
	  WHERE SalesPersonID IS NOT NULL
)
-- Outer Query
SELECT SC.SalesPersonID,
       COUNT(SC.SalesOrderID) AS TotalSales,
	   SC.SalesYear
FROM Sales_CTE AS SC
GROUP BY SC.SalesYear,SC.SalesPersonID
ORDER BY SC.SalesPersonID, SC.SalesYear;

-- Example 2
WITH Sales_CTE (SalesPersonID, NumberOfOrders)  
AS  
(  
    SELECT SalesPersonID, COUNT(*)  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
    GROUP BY SalesPersonID  
)  
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"  
FROM Sales_CTE;