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.

Diese Übung ist Teil des Kurses

Introduction to BigQuery

Kurs anzeigen

Anleitung zur Übung

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

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

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

Code bearbeiten und ausführen