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 exercício faz parte do curso

Introduction to BigQuery

Ver curso

Instruções do exercício

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.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

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 e executar o código