Optimizing using CTEs

CTEs also help you optimize queries by handling computations like aggregations before working with the data in the main query.

For example, you want to find the highest number of payments by order item count. This exercise will show you how to do that efficiently with a CTE.

Este ejercicio forma parte del curso

Introduction to BigQuery

Ver curso

Instrucciones del ejercicio

Add the correct aggregate functions in the subquery and the main query.
Use ARRAY_LENGTH to find the number of items in each order using the order_items column twice in the main query.

Ejercicio interactivo práctico

Prueba este ejercicio y completa el código de muestra.

WITH payments AS (
  SELECT
    -- Use the correct aggregate to find the highest number of payments
    ___(payment_sequential) AS num_payments,
    order_id
  FROM ecommerce.ecomm_payments 
  -- Group the results by order
  GROUP BY ___)
     
SELECT
  -- Add the correct function to find the length or number of order items
  ___(o.order_items) AS num_items,
  MAX(p.num_payments) AS max_payments
FROM ecommerce.ecomm_orders o
JOIN payments p
-- Add the correct keyword to join using the same column
___ (order_id)
-- Add the correct function to find the length or number of order items
GROUP BY ___(o.order_items)

Editar y ejecutar código