Common Table Expressions

WITH <CTE name>
AS
(<CTE_definition>)
<Outer Query referencing CTE>

SELECT 
    p.ProductName, 
    c.CategoryName, 
    p.UnitPrice 
FROM Products p JOIN Categories c 
ON c.CategoryID = p.CategoryID 
WHERE p.UnitPrice > 10.0

WITH products_and_categories (ProductName, CategoryName, UnitPrice) 
AS (SELECT p.ProductName, c.CategoryName, p.UnitPrice 
    FROM Products p JOIN Categories c 
    ON c.CategoryID = p.CategoryID 
    WHERE p.UnitPrice > 10.0) 
        
SELECT ProductName, CategoryName, UnitPrice 
FROM products_and_categories

WITH CTE_year 
AS (SELECT YEAR(orderdate) AS orderyear, customerid 
    FROM orders ) 
        
SELECT 
    orderyear, 
    count(DISTINCT customerid) AS cust_count 
FROM CTE_year 
GROUP BY orderyear