Using row based WINDOW functions

Window functions often take a row-based window, looking at a number of rows before or after the current row. In this exercise, you will use the same query three times to create a row based window function.

This exercise is part of the course

Introduction to BigQuery

View Course

Hands-on interactive exercise

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

-- Complete the query to find the rolling average

SELECT
  order_id,
  order_purchase_timestamp,
  -- Aggregate the price to find the average item price
  AVG(___) 
  OVER(
    -- Order the query by the purchase timestamp
    ORDER BY ___ 
    -- Start the rows window as a between statement
    ROWS ___
    -- Create the window for the nine previous rows
    ___ ___
    -- Stop the window at the current row
    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
ORDER BY order_purchase_timestamp;

Edit and Run Code