cte_2.sql

--multiple CTEs per statement

WITH
  ProductQty AS (
  	SELECT 
  		ProductId, 
  		LocationId, 
  		Shelf, 
  		Bin, 
  		Quantity
  	
  	FROM
  		Production.ProductInventory
  ),
    
  ListPriceHistory AS (
  	SELECT 
  		ProductId, 
  		StartDate, 
  		EndDate, 
  		ListPRice
    
  	FROM
  		Production.ProductListPriceHistory
  	
  	WHERE
  		ListPrice > 10.00
  )

SELECT 
  p.ProductId, 
  SUM(p.Quantity) AS SumQuantity

FROM
  ProductQty AS p
  
  INNER JOIN
    ListPriceHistory AS lp
      ON
        p.ProductId = lp.ProductId

GROUP BY
	p.ProductId;