USE AdventureWorks
GO

SELECT  City, NumberOfOrders = count(*)
FROM NewIndividual i  JOIN NewCustomer c  ON i.CustomerID = c.CustomerID AND c.CustomerType = 'I'
  JOIN NewCustomerAddress ca  ON ca.CustomerID = c.CustomerID
  JOIN NewAddress a ON a.AddressID = ca.AddressID
  JOIN NewSalesOrderHeader sh ON c.CustomerID = sh.CustomerID
  JOIN NewSalesOrderDetail sd ON sd.SalesOrderID = sh.SalesOrderID
  JOIN NewProduct p ON p.ProductID = sd.ProductID
WHERE StateProvinceID = (SELECT StateProvinceID FROM Person.StateProvince WHERE StateProvinceCode = 'CA')
GROUP BY City
ORDER BY 2 DESC
GO

