PDE Preparing and Using Data for Analysis Practice Question
You have a BigQuery table `sales` with columns `order_id`, `customer_id`, `order_date` (DATE), and `amount` (NUMERIC). You need to create a new table that contains, for each customer, the total sales amount and the date of their most recent order. The result should include `customer_id`, `total_amount`, and `last_order_date`. Which SQL query achieves this correctly?
⚠ Common exam trap
Candidates often confuse window functions like LAST_VALUE with aggregate functions, or incorrectly grouping by order_date and producing multiple rows per customer.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id
To compute per-customer aggregates, you use GROUP BY on `customer_id` and aggregate functions SUM and MAX. The SUM function totals the amounts, and MAX retrieves the latest order date. Other approaches using window functions or incorrect grouping do not produce the required single-row-per-customer result. The correct query is straightforward and uses standard SQL aggregation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SELECT customer_id, SUM(amount) AS total_amount, order_date AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id, order_date
Why it's wrong here
This query groups by both `customer_id` and `order_date`, which would produce multiple rows per customer (one per order date) rather than a single row with the most recent date. It also selects `order_date` without an aggregate function, which is allowed because it is in the GROUP BY, but it does not give the maximum date. The result would not match the requirement of one row per customer with the last order date.
- ✓
SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id
Why this is correct
This query groups by `customer_id` and uses SUM to aggregate total sales and MAX to find the most recent order date. It correctly produces one row per customer with the desired columns. The GROUP BY clause ensures aggregation per customer, and the aggregate functions operate on the appropriate columns. This is the standard and efficient way to compute such per-customer metrics in BigQuery.
- ✗
SELECT customer_id, SUM(amount) AS total_amount, LAST_VALUE(order_date) AS last_order_date FROM `project.dataset.sales` GROUP BY customer_id
Why it's wrong here
LAST_VALUE is a window function and requires an OVER clause; using it as an aggregate without OVER in a GROUP BY query is invalid. Even if used as a window function, it would not necessarily return the maximum date unless the window is ordered and framed correctly. The correct aggregate for the latest date is MAX. This query would fail to execute due to the missing OVER clause.
- ✗
SELECT customer_id, SUM(amount) AS total_amount, MAX(order_date) OVER (PARTITION BY customer_id) AS last_order_date FROM `project.dataset.sales`
Why it's wrong here
This query uses a window function but lacks a GROUP BY, so SUM(amount) is used as an aggregate without grouping, which would cause an error because `customer_id` is not aggregated. Even if SUM were replaced with a window function, the result would not be aggregated per customer; it would return one row per original order. The requirement is to aggregate per customer, which necessitates GROUP BY.
About these practice questions
One of 747 original PDE practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Google Cloud exam blueprint
This PDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PDE exam.