RANK and LEAD/LAG

Rankings allow you to rank your data in any particular order, while the LAG and LEAD functions allow you to look forward or backward in the window of our query. These tools help you to create compelling reports and more complex analytical functions in the case of LAG/LEAD. Here, you will put these into practice with our data.

Deze oefening maakt deel uit van de cursus

Introduction to BigQuery

Cursus bekijken

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

-- Complete the query to order customers by the total amount spent

-- First, write a CTE to group customers and find their total amount spent
WITH orders AS (
  SELECT
  -- Add the correct columns and aggregate functions
  ___,
  SUM(___) as all_items
  FROM ecommerce.ecomm_orders o, UNNEST(o.order_items) items
  JOIN ecommerce.ecomm_order_details od USING (order_id)
  GROUP BY od.customer_id
)

SELECT
	customer_id,
	all_items,
-- Fill in the RANK window function and OVER clause
RANK() ___(ORDER BY ___ DESC)
FROM orders
ORDER BY all_items DESC;

Code bewerken en uitvoeren