BaşlayınÜcretsiz Başlayın

Common Table Expressions

Pissa, the company you're consulting for, is planning a promotional campaign and needs your expertise.

The campaign aims to spotlight their most popular pizza based on total orders.

They're also considering introducing a value meal featuring their least expensive pizza.

Your task as a consulting data engineer is to identify both these pizzas.

Bu egzersiz

Introduction to Snowflake SQL

kursunun bir parçasıdır
Kursu Görüntüle

Egzersiz talimatları

  • Create a CTE named most_ordered and limit the results to 1.
  • Create another CTE, called cheapest_pizza and filter for the cheapest pizza using a subquery to find the minimum price.
  • Complete the query to select pizza_id and total_qty aliased as metric from most_ordered CTE.
  • Include pizza_id and price aliased as metric from cheapest_pizza CTE. Note, maintain the order of the columns.

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

-- Create a CTE named most_ordered and limit the results 
___ ___ ___ (
    SELECT pizza_id, SUM(quantity) AS total_qty 
    FROM order_details GROUP BY pizza_id ORDER BY total_qty DESC
    ___
)
-- Create CTE cheapest_pizza where price is equal to min price from pizzas table
, ___ ___ (
    SELECT pizza_id, price
    FROM pizzas 
    WHERE price = (SELECT ___(price) FROM pizzas)
    LIMIT 1
)

SELECT pizza_id, 'Most Ordered' AS Description, total_qty AS metric
-- Select from the most_ordered CTE
FROM ___
UNION ALL
SELECT pizza_id, 'Cheapest' AS Description, price AS metric
-- Select from the cheapest_pizza CTE
FROM ___
Kodu Düzenle ve Çalıştır