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.
Este ejercicio forma parte del curso
Introduction to BigQuery
Instrucciones del ejercicio
- Complete the query by filling in the window function and the
QUALIFYstatement to filter the query.
Ejercicio interactivo práctico
Prueba este ejercicio y completa el código de muestra.
-- 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;