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.

Deze oefening maakt deel uit van de cursus

Introduction to BigQuery

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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)

Code bewerken en uitvoeren