Filtering data with CTEs

One of the most effective ways to use a CTE is to filter your data in the CTE before using it later in the query. This reduces the overall cost of the query by bringing less data into the final query. This query will help us find the order status of orders with items that have a price over $150.

Este exercício faz parte do curso

Introduction to BigQuery

Ver curso

Instruções do exercício

Create a new CTE orders for orders with a price over $150, un-nesting order_items to find the price column.
Join the results of the CTE to the ecomm_order_details dataset to find the number of order_status and aggregate them by COUNTing the orders in each status.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

-- Add the correct items to finish our filtered CTE

-- Create a new CTE with the name orders
WITH ___ AS (
  SELECT order_id
  -- Add the correct column for the order item details
  FROM ecommerce.ecomm_orders, UNNEST(___) items
  -- Fill in the correct column for the item price 
  WHERE items.___ > 150
)

SELECT
	-- Aggregate to find the total number of orders
	___(order_id),
	-- Add the column for the status of the order
	___
FROM ecommerce.ecomm_order_details od
JOIN orders o USING (order_id)
GROUP BY order_status

Editar e executar o código