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.
Questo esercizio fa parte del corso
Introduction to Snowflake SQL
Istruzioni dell'esercizio
- Fill in the subquery to find the
AVGoftotal_quantity. - Calculate
total_quantitywithin the subquery. - Alias the subquery as
subquery.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
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
) ___
)