Query for Multiple CTEs

With CTE1 AS (

Select ProductID,sum(OrderQty) as TotalOrderQty from Sales.SalesOrderDetail

group by ProductID



),
CTE2 as(

Select p.ProductID,pc.ProductCategoryID, pc.Name from Production.Product p join Production.ProductSubcategory psc

on psc.ProductSubcategoryID=p.ProductSubcategoryID

join Production.ProductCategory pc

on pc.ProductCategoryID=psc.ProductCategoryID



)
Select * from (

Select CTE2.ProductCategoryId,CTE2.Name,CTE1.ProductId,CTE1.TotalOrderQty,

rn=ROW_NUMBER() over( PARTITION by CTE2.ProductCategoryId order by CTE1.TotalOrderQty desc)

from CTE1 join CTE2 on cte1.ProductID=cte2.ProductID

) CTE where rn<=3