Subqueries
Pissa, the expanding pizza delivery enterprise, is now using your expertise to identify some trends.
They want to streamline its pizza offerings by identifying under-performing pizzas. Your task is to find the pizza types ordered less frequently than the average for all types.
This exercise is part of the course
Introduction to Snowflake SQL
Exercise instructions
- Fill in the subquery to find the
AVGoftotal_quantity. - Calculate
total_quantitywithin the subquery. - Alias the subquery as
subquery.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT pt.name,
pt.category,
SUM(od.quantity) AS total_orders
FROM pizza_type pt
JOIN pizzas p
ON pt.pizza_type_id = p.pizza_type_id
JOIN order_details od
ON p.pizza_id = od.pizza_id
GROUP BY ALL
HAVING SUM(od.quantity) < (
-- Calculate AVG of total_quantity
SELECT ___(___)
FROM (
-- Calculate total_quantity
SELECT ___(___.___) AS total_quantity
FROM pizzas p
JOIN order_details od
ON p.pizza_id = od.pizza_id
GROUP BY p.pizza_id
-- Alias as subquery
) ___
)