Using a with statement, how would you show who ordered cat food, and the total amount of money spent?

with cat_food as (

Select Customer_ID, SUM(Price) as TOTAL_PRICE

From Orders

Where Item like "%Cat Food%"

Group by Customer_ID

)

Select Customer_name, TOTAL_PRICE

From Customers c

Inner JOIN cat_food f

ON c.Customer_ID = f.Customer_ID

where c.Customer_ID in (Select Customer_ID from cat_food);

Although this was a simple statement, the "with" clause really shines when a complex query needs to be run on a table before joining to another. With statements are nice, because you create a pseudo temp when running your query, instead of creating a whole new table.

The Sum of all the purchases of cat food weren't readily available, so we used a with statement to create the pseudo table to retrieve the sum of the prices spent by each customer, then join the table normally.