PDE Preparing and Using Data for Analysis Practice Question
You are preparing data in BigQuery for analysis. You have a table `orders` with columns `order_id`, `customer_id`, `order_date`, and `amount`. You need to create a new table that includes all orders, plus a column `prev_order_amount` that contains the amount of the customer's previous order by `order_date`. If there is no previous order, the value should be NULL. Which SQL feature should you use?
⚠ Common exam trap
Watch out — candidates often confuse LAG with LEAD, or thinking a self-join is necessary for previous-row access, when window functions are the intended solution.
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
✓
Use the LAG window function partitioned by `customer_id` and ordered by `order_date`.
The LAG window function is designed to access a previous row's value within a partition. By partitioning by customer and ordering by order date, it returns the amount of the customer's previous order. This is efficient and avoids self-joins. Other window functions like LEAD look forward, and FIRST_VALUE returns the first value in the partition, not the previous row.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the LAG window function partitioned by `customer_id` and ordered by `order_date`.
Why this is correct
The LAG window function accesses data from a previous row in the same result set without the need for a self-join. Partitioning by `customer_id` ensures that the previous order is for the same customer, and ordering by `order_date` defines the sequence. LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) returns the amount of the previous order, or NULL if there is no previous row. This is the most efficient and readable solution.
- ✗
Use the LEAD window function partitioned by `customer_id` and ordered by `order_date`.
Why it's wrong here
LEAD accesses data from a subsequent row, not a previous row. It would return the amount of the next order, not the previous one. For the scenario, you need the previous order amount, so LAG is the correct function. Using LEAD would give you the next order's amount, which is the opposite of what is required. This is a common confusion between LAG and LEAD.
- ✗
Use a self-join on `customer_id` where the previous order date is the maximum order date less than the current order date.
Why it's wrong here
A self-join can work, but it requires a correlated subquery or a join with a condition that finds the maximum order date less than the current order date for the same customer. This approach is inefficient and complex, especially with large datasets. It also may produce duplicate rows if there are multiple orders on the same date. Window functions are the standard and more efficient way to access previous rows.
- ✗
Use the FIRST_VALUE window function partitioned by `customer_id` and ordered by `order_date`.
Why it's wrong here
FIRST_VALUE returns the first value in the window frame, which would be the earliest order's amount for each customer, not the previous order's amount. It does not give you the immediately preceding row. To get the previous row, you need LAG. FIRST_VALUE is useful for getting the first order amount, but not for sequential previous values.
About these practice questions
Courseiva writes every PDE question from scratch — 747 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. 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.