Get startedGet started for free

Early filtering

Pissa has now asked for your expertise to optimize the performance of their database queries. They suspect that their existing queries are not efficient enough and take too long to run.

The goal is to retrieve the orders made after November 01, 2015, and only the pizzas in the 'Veggie' category.

Complete the given SQL query by implementing early filtering techniques.

This exercise is part of the course

Introduction to Snowflake SQL

View Course

Exercise instructions

  • Complete the filtered_orders CTE, filtering only to include orders made after 2015-11-01.
  • Complete the filtered_pizza_type CTE, filtering only to include pizzas in the 'Veggie' category.
  • Retrieve the records from the filtered_orders CTE.
  • Join the filtered_pizza_type CTE based on the pizza_type_id column using ON clause.

Hands-on interactive exercise

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

WITH filtered_orders AS (
  SELECT order_id, order_date 
  FROM orders 
  -- Filter records where order_date is greater than November 1, 2015
  ___
)

, filtered_pizza_type AS (
  SELECT name, pizza_type_id 
  FROM pizza_type 
  -- Filter the pizzas which are in the Veggie category
  ___
)

SELECT fo.order_id, fo.order_date, fpt.name, od.quantity
-- Get the details from filtered_orders CTE
FROM ___ AS fo
JOIN order_details AS od ON fo.order_id = od.order_id
JOIN pizzas AS p ON od.pizza_id = p.pizza_id
-- JOIN the filtered_pizza_type CTE on pizza_type_id
___ ___ AS fpt ON p.pizza_type_id = ___.___
Edit and Run Code