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.

Questo esercizio fa parte del corso

Introduction to BigQuery

Visualizza il corso

Istruzioni dell'esercizio

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.

Esercizio pratico interattivo

Prova a risolvere questo esercizio completando il codice di esempio.

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)

Modifica ed esegui il codice