Filtering with QUALIFY

If you ever need to use the results of a window function like a WHERE filter then you need to use QUALIFY. In this query you will find all the times when the rolling average of the current and previous 9 rows averaged over $500.

This exercise is part of the course

Introduction to BigQuery

View Course

Exercise instructions

Complete the query by filling in the window function and the QUALIFY statement to filter the query.

Hands-on interactive exercise

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

-- Complete the query to find all times when the rolling average is over $500

SELECT
  order_id,
  order_purchase_timestamp,
  -- Complete the rolling average for the current and nine previous rows
  AVG(___) 
  OVER(ORDER BY ___ 
       ROWS BETWEEN ___ ___ AND ___ ROW) as rolling_avg
FROM ecommerce.ecomm_order_details od
JOIN ecommerce.ecomm_orders o 
USING (order_id), unnest(o.order_items) as item
-- Add the qualify statement to find all rows with an average over $500
QUALIFY ___ ___
ORDER BY order_purchase_timestamp;

Edit and Run Code