Aan de slagGa gratis aan de slag

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.

Deze oefening maakt deel uit van de cursus

Introduction to Snowflake SQL

Cursus bekijken

Oefeninstructies

  • Fill in the subquery to find the AVG of total_quantity.
  • Calculate total_quantity within the subquery.
  • Alias the subquery as subquery.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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
  ) ___
)
Code bewerken en uitvoeren