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.

Diese Übung ist Teil des Kurses

Introduction to BigQuery

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

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 bearbeiten und ausführen