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.

This exercise is part of the course

Introduction to BigQuery

View Course

Exercise instructions

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.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- 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

Edit and Run Code